package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Iterables;
import com.google.common.collect.Maps;
import com.iris.live.services.common.reconsitution.ApprovalWorkFlowEnum;
import com.iris.live.services.data.models.Lsh经销商表Model;
import com.iris.live.services.data.models.Lsh车型Model;
import com.iris.live.services.data.models.PushApprovalWorkFlowModel;
import com.iris.live.services.data.models.报价单表Model;
import com.iris.live.services.data.repositories.Lsh经销商表Repository;
import com.iris.live.services.data.repositories.Lsh车型Repository;
import com.iris.live.services.data.repositories.PushApprovalWorkFlowRepository;
import com.iris.live.services.data.repositories.报价单表Repository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.ApprovalService;
import com.iris.live.services.services.SetService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 */
@Repository
public class PushApprovalWorkFlowRepositoryImpl extends BaseRepository implements PushApprovalWorkFlowRepository {
    @Autowired
    private Lsh经销商表Repository lsh经销商表Repository;

    @Autowired
    private 报价单表Repository qRep;

    @Autowired
    private Lsh车型Repository carRep;

    private Logger logger = LoggerFactory.getLogger(PushApprovalWorkFlowRepositoryImpl.class);

    @Override
    public PageData getPrivilegeForCarModel(String carBrand, String carClass, String carModel, Integer size, Integer index) {
        StringBuilder sb = new StringBuilder();
        Map<String, Object> params = new HashMap<>();
        sb.append("select  \n" +
                "  许可 as dlr,  \n" +
                "  设置项目 as item,  \n" +
                "  值 as val,  \n" +
                "  更新账号 as user,  \n" +
                "  更新时间 as dt  \n" +
                "from itask基础设置  \n" +
                "where \n" +
                " 设置项目 like 'step%'  \n"
        );
        if (carModel != null && carModel.length() != 0) {
            sb.append("and 设置项目 like :params  \n");
            params.put("params", "%" + carBrand.trim() + "__" + carClass.trim() + "__" + carModel.trim() + "%");
        } else if (carClass != null && carClass.length() != 0) {
            sb.append("and 设置项目 like :params  \n");
            params.put("params", "%" + carBrand.trim() + "__" + carClass.trim() + "%");
        } else if (carBrand != null && carBrand.length() != 0) {
            sb.append("and 设置项目 like :params  \n");
            params.put("params", "%" + carBrand.trim() + "%");
        } // 参数全为空，没有过滤条件
        sb.append("order by substr(设置项目, 8), substr(设置项目, 5, 1)  \n");
        return getPage(sb.toString(), params, size, index);
    }

    @Override
    public PageData exportPrivilegeForCarModel(String carBrand, String carClass, String carModel, Integer size, Integer index) {
        StringBuilder sb = new StringBuilder();
        Map<String, Object> params = new HashMap<>();
        sb.append("select  * \n" +
                "from snp_business_set_logs  \n" +
                "where \n" +
                " 1=1  \n"
        );
        if (carModel != null && carModel.length() != 0) {
            sb.append("and car_model like :carModel  \n");
            params.put("carModel", "%" + carModel.trim() + "%");
        } else if (carClass != null && carClass.length() != 0) {
            sb.append("and car_series like :carClass  \n");
            params.put("carClass", "%" + carClass.trim() + "%");
        } else if (carBrand != null && carBrand.length() != 0) {
            sb.append("and brand like :brand  \n");
            params.put("brand", "%" + carBrand.trim() + "%");
        } // 参数全为空，没有过滤条件
        sb.append("order by id desc \n");
        return getPage(sb.toString(), params, size, index);
    }

    @Override
    public PageData getPrivilegeForCarModelDealer(String dlr, String carBrand, String carClass, String carModel, Integer size, Integer index) {
        StringBuilder sb = new StringBuilder();
        Map<String, Object> params = new HashMap<>();
        sb.append("select  \n" +
                "  username as '账号',  \n" +
                "  a.step as '审批层级',  \n" +
                "  (select 角色 from 用户 where 用户名 = username) as '角色',  \n" +
                "  (select 人员姓名 from 用户 where 用户名 = username) as '姓名',  \n" +
                "  dlr,  \n" +
                "  item,  \n" +
                "  val,  \n" +
                "  val as '审批权限',  \n" +
                "  user,  \n" +
                "  dt  \n" +
                "from (  \n" +
                "  select permit, step1 as 'username', '销售顾问' as step from pushApprovalItemSetting where permit = :dlr and type = 3 and step1 is not null  \n" +
                "  union all  \n" +
                "  select permit, step2 as 'username', '一级审批' as step from pushApprovalItemSetting where permit = :dlr and type = 3 and step2 is not null  \n" +
                "  union all  \n" +
                "  select permit, step3 as 'username', '二级审批' as step from pushApprovalItemSetting where permit = :dlr and type = 3 and step3 is not null  \n" +
                "  union all  \n" +
                "  select permit, step4 as 'username', '三级审批' as step from pushApprovalItemSetting where permit = :dlr and type = 3 and step4 is not null  \n" +
                "  union all  \n" +
                "  select permit, step5 as 'username', '四级审批' as step from pushApprovalItemSetting where permit = :dlr and type = 3 and step5 is not null  \n" +
                ") a left join (  \n" +
                "  select  \n" +
                "    许可 as dlr,  \n" +
                "    设置项目 as item,  \n" +
                "    值 as val,  \n" +
                "    更新账号 as user,  \n" +
                "    更新时间 as dt,  \n" +
                "    case  \n" +
                "      when left(设置项目, 5) = 'step1' then '销售顾问'  \n" +
                "      when left(设置项目, 5) = 'step2' then '一级审批'  \n" +
                "      when left(设置项目, 5) = 'step3' then '二级审批'  \n" +
                "      when left(设置项目, 5) = 'step4' then '三级审批'  \n" +
                "      when left(设置项目, 5) = 'step5' then '四级审批'  \n" +
                "    end as 'step'  \n" +
                "  from itask基础设置  \n" +
                "  where 许可 = :dlr  \n" +
                "  and 设置项目 like 'step%'  \n" +
                ") b on a.step = b.step  \n"
        );
        params.put("dlr", dlr);
        if (carModel != null && carModel.length() != 0) {
            sb.append("and item like :params  \n");
            params.put("params", "%" + carBrand.trim() + "__" + carClass.trim() + "__" + carModel.trim() + "%");
        } else if (carClass != null && carClass.length() != 0) {
            sb.append("and item like :params  \n");
            params.put("params", "%" + carBrand.trim() + "__" + carClass.trim() + "%");
        } else if (carBrand != null && carBrand.length() != 0) {
            sb.append("and item like :params  \n");
            params.put("params", "%" + carBrand.trim() + "%");
        } // 参数全为空，没有过滤条件
        sb.append("order by substr(item, 8), substr(item, 5, 1)  \n");
        return getPage(sb.toString(), params, size, index);
    }

    @Override
    public List getApprovalList(String permit, String type, String quotedPriceCodeOrVIN) {
        String sql;
        Map<String, String> params;
        // 调车时要区分type，非调车时，不区分，可能是意向订单到意向订单修改
        if ("8".equals(type) || "9".equals(type)) {
            sql = "select \n" +
                    "	a.id, " +
                    "  	a.transcation_code, " +
                    "  	a.step," +
                    "  	a.quotedPriceCodeOrVIN," +
                    "  	a.flag, " +
                    "  	a.permit, " +
                    "  	a.type, " +
                    "   a.userName, " +
                    "   (select 人员姓名 from 用户 where 用户名 = a.userName) as userNameCN,  " +
                    "  	a.approvalStatus, " +
                    "  	a.remark, " +
                    "  	a.processTime, " +
                    "  	b.transcation_status, " +
                    "  	b.transcationCode " +
                    "from pushApprovalWorkFlow a\n" +
                    "join (\n" +
                    "	select if(locate(2, statuses), 'close', 'process') as transcation_status, transcation_code as transcationCode\n" +
                    "	from (\n" +
                    "		select group_concat(approvalStatus) as statuses, transcation_code\n" +
                    "		from pushApprovalWorkFlow\n" +
//                "		where permit = :permit\n" +
                    "		where type = :type\n" +
                    "		and quotedPriceCodeOrVIN = :quotedPriceCodeOrVIN\n" +
                    "		group by transcation_code\n" +
                    "	) temp\n" +
                    ") b on a.transcation_code = b.transcationCode\n" +
                    "where type=:type \n";
            params = Maps.newHashMap();
//        params.put("permit", permit);
            params.put("type", type);
            params.put("quotedPriceCodeOrVIN", quotedPriceCodeOrVIN);
        } else {
            sql = "select \n" +
                    "	a.id, " +
                    "  	a.transcation_code, " +
                    "  	a.step," +
                    "  	a.quotedPriceCodeOrVIN," +
                    "  	a.flag, " +
                    "  	a.permit, " +
                    "  	a.type, " +
                    "   a.userName, " +
                    "   (select 人员姓名 from 用户 where 用户名 = a.userName) as userNameCN,  " +
                    "  	a.approvalStatus, " +
                    "  	a.remark, " +
                    "  	a.processTime, " +
                    "  	b.transcation_status, " +
                    "  	b.transcationCode " +
                    "from pushApprovalWorkFlow a\n" +
                    "join (\n" +
                    "	select if(locate(2, statuses), 'close', 'process') as transcation_status, transcation_code as transcationCode\n" +
                    "	from (\n" +
                    "		select group_concat(approvalStatus) as statuses, transcation_code\n" +
                    "		from pushApprovalWorkFlow\n" +
//                "		where permit = :permit\n" +
//                "		where type = :type\n" +
                    "		where quotedPriceCodeOrVIN = :quotedPriceCodeOrVIN\n" +
                    "		group by transcation_code\n" +
                    "	) temp\n" +
                    ") b on a.transcation_code = b.transcationCode\n";
//                "where type=:type \n";
            params = Maps.newHashMap();
//        params.put("permit", permit);
//        params.put("type", type);
            params.put("quotedPriceCodeOrVIN", quotedPriceCodeOrVIN);
        }
        return this.getList(sql, params);
    }

    @Override
    public Double preContractSnpProcess(Integer quotedPriceCode) {
        String sql = " SELECT ifnull(standard_configuration_area_price_limit_SNP_tax_included, 0) as snp " +
                " FROM snp_setting a " +
                " join 报价单表 b ON a.permit = b.`许可` and b.`报价编号` = :quotedPriceCode " +
                " WHERE a.carCode in ( " +
                " select a.车型编号 from lsh车型 a " +
                " where EXISTS( " +
                " select * from ( " +
                " select b.来源,b.品牌,b.级别,b.车型,b.车型详细 " +
                " from 报价单表 a " +
                " join lsh车型 b on a.车型编号 = b.车型编号 " +
                " where a.报价编号 = :quotedPriceCode " +
                " ) b where a.来源 = b.来源 " +
                " and a.品牌 = b. 品牌 " +
                " and a.级别 = b.级别 " +
                " and a.车型 = b.车型 " +
                " and a.车型详细 = b.车型详细 " +
                " ) " +
                " ) and a.y_m = '190001' " +
                " AND ( a.apperanceCode in " +
                " ( select n.颜色代码 FROM 报价单表 m " +
                " JOIN lsh库存表 n on m.库存编号 = n.库存编号 where m.报价编号 = :quotedPriceCode" +
                " ) or a.apperanceCode = 0 ) " +
                " ORDER BY a.apperanceCode DESC LIMIT 1 ";
        Map<String, Integer> params = Maps.newHashMap();
        params.put("quotedPriceCode", quotedPriceCode);
        List list = getList(sql, params);
        Object obj0 = Iterables.getFirst(list, null);
        if (list.size() == 0 || ((Map) obj0).get("snp") == null) {
            return null;
        } else {
            double snp;
            try {
                snp = Double.valueOf(((Map) obj0).get("snp").toString());
            } catch (Exception e) {
                logger.error("snp值不能强转成Double", e);
                return null;
            }
            return snp;
        }
    }

    // 通过许可，类型，业务ID，将审批设置表的相关记录列转行，并且转换成审批流水
    @Override
    public List<PushApprovalWorkFlowModel> constructWorkFlowModelList(String permit, Integer type, String id) {
        String sql =
                "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step1' as step, step1 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step1 is not null and step1 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step2' as step, step2 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step2 is not null and step2 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step3' as step, step3 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step3 is not null and step3 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step4' as step, step4 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step4 is not null and step4 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step5' as step, step5 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step5 is not null and step5 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step6' as step, step6 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step6 is not null and step6 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step7' as step, step7 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step7 is not null and step7 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step8' as step, step8 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step8 is not null and step8 <> '' union all  \n" +
                        "select concat(:id, '_', permit, '_', type, '_', current_date(), '_', current_time()) as transactionCode, 'step9' as step, step9 as userName from pushApprovalItemSetting where permit = :permit and type = :type and step9 is not null and step9 <> ''  \n";
        Map<String, Object> params = Maps.newHashMap();
        params.put("id", id);
        params.put("permit", permit);
        params.put("type", type);
        List list = this.getList(sql, params);
        List<PushApprovalWorkFlowModel> reList = new ArrayList<>();
        for (Object o : list) {
            Map map = (Map) o;
            PushApprovalWorkFlowModel pModel = new PushApprovalWorkFlowModel();
            pModel.setId(0);
            pModel.setTranscationCode((String) map.get("transactionCode"));
            pModel.setStep((String) map.get("step"));
            pModel.setQuotedPriceCodeOrVin(id);
            pModel.setPermit(permit);
            pModel.setType(type);
            pModel.setUserName((String) map.get("userName"));
            pModel.setApprovalStatus("0");
            pModel.setRemark("");
            pModel.setProcessTime(Timestamp.valueOf(LocalDateTime.now()));
            reList.add(pModel);
        }
        return reList;
    }

    /**
     * 获取订单审批list
     */
    @Override
    public PageData getOrderApprovalList(int pageSize, int pageIndex, String type,
                                         String permit, String userName,
                                         ApprovalService.GetApprovalListRequest reqeust) {
        StringBuilder sql = new StringBuilder();
        // 订单合同审批列表
        sql.append("select * from (SELECT      \n" +
                        "   t2.报价编号,     \n" +
                        "   t2.许可,     \n" +
                        "   t2.许可 as 许可_bmbs,  \n" +
                "   0 as 车型外观编号,     \n" +
                        "   t2.车型编号,     \n" +
                        "   (select 人员姓名 from 用户 where 用户名 = t2.销售顾问) as '人员姓名',     \n" +
                        "   if((select 车型详细 from lsh车型 where 车型编号 = t2.车型编号) = '', (select 车型 from lsh车型 where 车型编号 = t2.车型编号), (select 车型详细 from lsh车型 where 车型编号 = t2.车型编号)) as '车型详细',     \n" +
                        "   (select 顾客姓名 from 顾客记录汇总 where 顾客编号 = t2.顾客编号) as '顾客姓名',    " +
                        "   (select 经销商名 from lsh经销商表 where 许可_bmbs = t2.许可) as '经销商名',     \n" +
                        "   bb.quotedPriceCodeOrVIN,  \n" +
                        "   bb.approvalStatus,  \n" +
                        "   bb.transcation_code,  \n" +
                        "   bb.type,  \n" +
                        "   bb.transcation_code_time,  \n" +
                        "   bb.订单申请日期  \n" +
                        "FROM 报价单表 t2      \n" +
                        "JOIN (       \n" +
                        "	SELECT DISTINCT " +
                        "       quotedPriceCodeOrVIN,  \n" +
                        "       approvalStatus,  \n" +
                        "       a.transcation_code,  \n" +
                        "       type,  \n" +
                        "       REPLACE(SUBSTR(a.transcation_code, -19), '/', '-') AS  transcation_code_time,      \n" +
                        "       left(REPLACE(SUBSTR(a.transcation_code, -19), '/', '-'), 10) AS 订单申请日期  \n" +
                        "	FROM pushApprovalWorkFlow  a    \n" +
                        "	JOIN (        \n" +
                        "		SELECT transcation_code      \n" +
                        "		FROM (      \n" +
                        "			SELECT aa.transcation_code,   \n" +
                        "				-- 在当前审批人之前的审批历史中查找审批状态为未处理的记录   \n" +
                        "				LOCATE('0', SUBSTR(GROUP_CONCAT(approvalStatus ORDER BY bb.step), 1, LENGTH(GROUP_CONCAT(approvalStatus ORDER BY bb.step)) - 1)) AS todoFlag,      \n" +
                        "				-- 在当前审批人之前的审批历史中查找审批状态为驳回的记录   \n" +
                        "				LOCATE('2', SUBSTR(GROUP_CONCAT(approvalStatus ORDER BY bb.step), 1, LENGTH(GROUP_CONCAT(approvalStatus ORDER BY bb.step)) - 1)) AS reject,   \n" +
                        "               -- 自动处理的条目不显示  \n" +
                        "               locate('3', right(group_concat(approvalStatus order by bb.step), 1)) as autoApproval" +
                        "			FROM (      \n" +
                        "				-- 将当前审批人所在的审批step记录在current_step   \n" +
                        "				SELECT (@a\\:=step) AS current_step, transcation_code      \n" +
                        "				FROM pushApprovalWorkFlow      \n" +
                        "				WHERE TYPE IN (1, 2, 3, 4, 5, 6, 7, 10, 12, 13) AND userName= :userName   \n"
        );
        if (reqeust.appstatus != null && !"".equals(reqeust.appstatus)) {
            sql.append(" and approvalStatus='").append(reqeust.appstatus).append("' ");
        } else {
            sql.append(" and approvalStatus='0' ");
        }
        sql.append(" order by id desc limit 2000  ");
        sql.append("     ) aa      \n" +
                "			JOIN pushApprovalWorkFlow bb ON aa.transcation_code = bb.transcation_code      \n" +
                "			-- 过滤当前step之后的审批流程   \n" +
                "			WHERE aa.current_step >= bb.step      \n" +
                "			GROUP BY transcation_code, aa.current_step      \n" +
                "		) a      \n" +
                "		-- 只显示之前已经全部处理完，且没有驳回状态的记录   \n" +
                "		WHERE todoFlag = 0 AND reject = 0 and autoApproval = 0       \n" +
                "	) b on a.`transcation_code` = b.transcation_code   \n" +
                "	WHERE userName = :userName    \n" +
                ") bb ON t2.报价编号 = bb.quotedPriceCodeOrVIN   " +
                ") cc where 1 = 1   "
        );
        if (reqeust.truepermit != null && !"".equals(reqeust.truepermit)) {
            sql.append(" and  cc.经销商名 like '%").append(reqeust.truepermit).append("%'  ");
        }
        if (reqeust.type != null && !"".equals(reqeust.type)) {
            String[] typeStr = reqeust.type.split(",");
            sql.append(" and ( cc.type in (    ");
            for (int i = 0; i < typeStr.length; i++) {
                if (i != typeStr.length - 1) {
                    sql.append(typeStr[i]).append(",");
                } else {
                    sql.append(typeStr[i]).append(" ");
                }
            }
            sql.append(")  )");
        }
//        if (reqeust.appstatus != null && !"".equals(reqeust.appstatus)) {
//            sql.append(" and  bb.approvalStatus='").append(reqeust.appstatus).append("'  ");
//            sql = sql.replaceAll("%app_status%", " and approvalStatus='" + reqeust.appstatus + "'  ");
//        } else {
//            sql = sql.replaceAll("%app_status%", " and approvalStatus='0'  ");
//        }
        if (reqeust.clientName != null && !"".equals(reqeust.clientName)) {
            sql.append(" and  cc.顾客姓名 like  '%").append(reqeust.clientName).append("%'  ");
        }
        if (reqeust.cartypes != null && !"".equals(reqeust.cartypes)) {
            sql.append(" and  cc.车型详细  like  '%").append(reqeust.cartypes).append("%'  ");
        }
        if (reqeust.soleman != null && !"".equals(reqeust.soleman)) {
            sql.append(" and  cc.人员姓名  like  '%").append(reqeust.soleman).append("%'  ");
        }
        if (reqeust.starDate != null && !"".equals(reqeust.starDate)) {
            if (reqeust.endDate != null && !"".equals(reqeust.endDate)) {
                sql.append(" and  (cc.订单申请日期 >='").append(reqeust.starDate).append("'  and   ")
                        .append("         cc.订单申请日期 <='").append(reqeust.endDate).append("')    ");
            } else {
                sql.append(" and  cc.订单申请日期 >='").append(reqeust.starDate).append("'  ");
            }
        } else {
            if (reqeust.endDate != null && !"".equals(reqeust.endDate)) {
                sql.append(" and  cc.订单申请日期 <='").append(reqeust.endDate).append("'  ");
            }
        }
        sql.append("   ORDER BY transcation_code_time DESC    \n");

        Map<String, String> params = Maps.newHashMap();
//        if (type.contains("8") || type.contains("9")) {
//            params.put("permit", permit);
//        }
        params.put("userName", userName);
        return this.getPage(sql.toString(), params, pageSize, pageIndex);
    }

    @Override
    public List getCarTypeList(ApprovalService.GetApprovalListRequest reqeust) {
        String sql = "SELECT 车型详细      \n" +
                "FROM (     \n" +
                "	SELECT  t2.报价编号,   " +
                "		t4.车型详细     \n" +
                "	FROM 报价订单表 t1     \n" +
                "	JOIN 报价单表 t2 ON t1.订单编号 = t2.订单编号     \n"
                + "	JOIN 用户 t3 ON t3.用户名 = t2.销售顾问     " +
                "    join 顾客记录汇总 t7 on t2.顾客编号=t7.顾客编号   " +
                "    join Lsh经销商表 t8 on t8.`许可_BMBS`=t2.许可             \n" +
                "	JOIN LSH车型 t4 ON t2.车型编号 = t4.车型编号     \n" +
                "	LEFT JOIN LSH库存表 t5 ON t5.库存编号=t2.库存编号     \n" +
                ") aa     \n" +
                "JOIN (       \n" +
                "	SELECT DISTINCT quotedPriceCodeOrVIN, approvalStatus, a.transcation_code, type, REPLACE(SUBSTR(a.transcation_code,-19), '/', '-') AS  transcation_code_time      \n" +
                "	FROM pushApprovalWorkFlow  a    \n" +
                "	JOIN (        \n" +
                "		SELECT transcation_code      \n" +
                "		FROM (      \n" +
                "			SELECT aa.transcation_code,   \n" +
                "				-- 在当前审批人之前的审批历史中查找审批状态为未处理的记录   \n" +
                "				LOCATE('0', SUBSTR(GROUP_CONCAT(approvalStatus ORDER BY bb.step), 1, LENGTH(GROUP_CONCAT(approvalStatus ORDER BY bb.step)) - 1)) AS todoFlag,      \n" +
                "				-- 在当前审批人之前的审批历史中查找审批状态为驳回的记录   \n" +
                "				LOCATE('2', SUBSTR(GROUP_CONCAT(approvalStatus ORDER BY bb.step), 1, LENGTH(GROUP_CONCAT(approvalStatus ORDER BY bb.step)) - 1)) AS reject,   \n" +
                "               -- 自动处理的条目不显示  \n" +
                "               locate('3', right(group_concat(approvalStatus order by bb.step), 1)) as autoApproval" +
                "			FROM (      \n" +
                "				-- 将当前审批人所在的审批step记录在current_step   \n" +
                "				SELECT (@a\\:=step) AS current_step, transcation_code, step      \n" +
                "				FROM pushApprovalWorkFlow      \n" +
                "				WHERE TYPE IN (1, 2, 3, 4, 5, 6, 7,10,11,12,13) AND userName= :userName   \n" +
                "			) aa      \n" +
                "			JOIN pushapprovalworkflow bb ON aa.transcation_code = bb.transcation_code      \n" +
                "			-- 过滤当前step之后的审批流程   \n" +
                "			WHERE aa.current_step >= bb.step      \n" +
                "			GROUP BY transcation_code, aa.current_step      \n" +
                "		) a      \n" +
                "		-- 只显示之前已经全部处理完，且没有驳回状态的记录   \n" +
                "		WHERE todoFlag = 0 AND reject = 0 and autoApproval = 0       \n" +
                "	) b on a.`transcation_code` = b.transcation_code   \n" +
                "	WHERE userName = :userName    \n" +
                ") bb ON aa.报价编号 = bb.quotedPriceCodeOrVIN   " +
                " where 1=1  ORDER BY transcation_code_time DESC   ";
        Map<String, String> params = Maps.newHashMap();

        params.put("userName", reqeust.userName);
        return this.getList(sql, params);


    }


//    /**
//     * 获取snp
//     */
//    @Override
//    public List getSNP(String carCode, String apperanceCode, String quotedPriceCode) {
//        // 算法  (车辆销售价 - 赠送成本) / (SNP + 加减配价格) - 100%
//        // 获取 (车辆销售价 - 赠送成本)（都是含税）
//        String sql1 = "SELECT  \n" +
//                "	IFNULL(a.折后价, 0) - ifnull(消费税, 0) - ifnull(b.随车成本, 0) * 1.17 - ifnull(赠送精品成本, 0) - ifnull(赠送延保成本, 0)  AS '车辆销售价 - 赠送成本'  \n" +
//                "FROM 报价单表 a  \n" +
//                "left JOIN (  \n" +
//                "	SELECT SUM(IFNULL(随车成本, 0)) 随车成本, 报价编号  \n" +
//                "	FROM 报价随车表  \n" +
//                "	WHERE 随车类型 = '经销商' AND 随车部门='销售部'  \n" +
//                "	group by 报价编号  \n" +
//                ") b on a.报价编号 = b.报价编号  \n" +
//                "left join (  \n" +
//                "	select sum(ifnull(成本, 0) )赠送精品成本, 编号  \n" +
//                "	from 报价精品详细表  \n" +
//                "	where 折后价 = 0  \n" +
//                "   group by 编号" +
//                ") c on a.精品 = c.编号  \n" +
//                "left join (  \n" +
//                "	select ifnull(延保原价, 0) 赠送延保成本, 延保编号  \n" +
//                "	FROM 报价五星延保  \n" +
//                "	WHERE 延保折后价 = 0  \n" +
//                ") d on a.延保编号 = d.延保编号  \n" +
//                "WHERE a.报价编号 = :quotedPriceCode  \n";
//        Map<String, String> params = Maps.newHashMap();
//        params.put("quotedPriceCode", quotedPriceCode);
//        List list1 = getList(sql1, params);
//
//        // 获取分母中的SNP
//        Double snp = preContractSnpProcess(Integer.parseInt(quotedPriceCode));
//        BigDecimal snpBig = BigDecimal.valueOf(0);
//        //判断snp的值，如果snp为null或小于0，从LSH车型表取MSRP；
//        if (snp == null || snp < 0) {
//            String sqlSNP = "select IFNULL(c.MSRP,0) as snp  " +
//                    "from 报价单表 a  " +
//                    "join LSH车型 c on a.车型编号 = c.`车型编号` " +
//                    "where a.报价编号 = :quotedPriceCode  ";
//            List listSNP = getList(sqlSNP, params);
//            if (listSNP != null && listSNP.size() > 0) {
//                snpBig = BigDecimal.valueOf(((BigInteger) (((Map) listSNP.get(0)).get("snp"))).intValue());
//            }
//        } else {
//            snpBig = new BigDecimal(snp);
//        }
//        // 获取加减配
//        // 20170312 姜老师的说法
//        String sql2 = "select IFNULL(b.`实际MSRP含税`,0) - IFNULL(b.标配msrp含税,0) AS 加减配价格  " +
//                "from 报价单表 a  " +
//                "join LSH库存表 b on a.库存编号 = b.库存编号  " +
//                "where a.报价编号 = :quotedPriceCode  ";
//        List list2 = getList(sql2, params);
//
//        BigDecimal a = new BigDecimal(0);
//        BigDecimal b = new BigDecimal(0);
//        if (list1.size() != 0 && list2.size() != 0) {
//            Map m1 = (Map) list1.get(0);
//            Double a1 = (Double) m1.get("车辆销售价 - 赠送成本");
//            a = new BigDecimal(a1, MathContext.DECIMAL32);
//            b = (BigDecimal) ((Map) list2.get(0)).get("加减配价格");
//        }
//        Map reMap = Maps.newHashMap();
//        DecimalFormat df1 = new DecimalFormat("####0.00");
//        DecimalFormat df2 = new DecimalFormat("####0.0000");
//        List reList = new ArrayList();
//        if (a != null && b != null) {
//            reMap.put("numerator", df1.format(a));
//            reMap.put("denominator", df1.format(snpBig.add(b)));
//            if (snpBig.add(b).compareTo(new BigDecimal(0)) == 0) {
//                reMap.put("result", "1");
//            } else {
//                reMap.put("result", df2.format(Double.parseDouble(a.divide(snpBig.add(b), 4, BigDecimal.ROUND_HALF_EVEN).toString()) - 1.0));
//            }
//
//            reList.add(reMap);
//        }
//        return reList;
//    }


    @Override
    public List getSNP(String carCode, String appearanceCode, String quoNum) {
        // SNP = (车辆销售价 - 赠送成本) / (SNP + 加减配价格) - 100%
        // 计算 车辆销售价 - 赠送成本
        String sql = "SELECT  \n" +
                "		A.`折后价` / 1.17  \n" +
                "		- ifnull(消费税, 0) / 1.17  \n" +
                "		- ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
                "		- ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
                "		- ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0) as top  \n" +
                "FROM 报价单表 A, LSH库存表 D  \n" +
                "WHERE A.`库存编号` = D.`库存编号`  \n" +
                "AND A.报价编号 = :quoNum  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("quoNum", quoNum);
        List topList = getList(sql, params);

        // 获取分母中的设置SNP
        Double doubleSetSnp = preContractSnpProcess(Integer.parseInt(quoNum));
        BigDecimal bdSetSnp = BigDecimal.valueOf(0);
        // 判断snp的值，如果snp为null或小于0，从LSH车型表取MSRP
        if (doubleSetSnp == null || doubleSetSnp < 0) {
            String sqlSNP = "select IFNULL(c.MSRP,0) as snp  " +
                    "from 报价单表 a  " +
                    "join LSH车型 c on a.车型编号 = c.`车型编号` " +
                    "where a.报价编号 = :quoNum  ";
            List listSNP = getList(sqlSNP, params);
            if (listSNP != null && listSNP.size() > 0) {
                bdSetSnp = BigDecimal.valueOf(((BigInteger) (((Map) listSNP.get(0)).get("snp"))).intValue());
            }
        } else {
            bdSetSnp = new BigDecimal(doubleSetSnp);
        }

        // 获取加减配 20170312 姜老师的说法
        String sql2 = "select IFNULL(b.`实际MSRP含税`,0) - IFNULL(b.标配msrp含税,0) AS 加减配价格  " +
                "from 报价单表 a  " +
                "join LSH库存表 b on a.库存编号 = b.库存编号  " +
                "where a.报价编号 = :quoNum  ";
        List list2 = getList(sql2, params);

        // 开始计算
        BigDecimal bdTop = new BigDecimal(0);
        BigDecimal bdAsSet = new BigDecimal(0);
        if (topList.size() != 0 && list2.size() != 0) {
            Map m1 = (Map) topList.get(0);
            Double doubleGp1d5 = (Double) m1.get("top");
            bdTop = new BigDecimal(doubleGp1d5);
            bdAsSet = (BigDecimal) ((Map) list2.get(0)).get("加减配价格");
        }
        Map<String, Object> reMap = Maps.newHashMap();
        DecimalFormat df1 = new DecimalFormat("####0.00");
        DecimalFormat df2 = new DecimalFormat("####0.0000");
        BigDecimal tax = new BigDecimal(1.17);
        List<Map> reList = new ArrayList<>();
        if (bdAsSet != null) {
            reMap.put("numerator", df1.format(bdTop));
            reMap.put("denominator", df1.format(bdSetSnp.add(bdAsSet).divide(tax, 4, BigDecimal.ROUND_HALF_EVEN)));
            if (bdSetSnp.add(bdAsSet).compareTo(new BigDecimal(0)) == 0) {
                reMap.put("result", "1");
            } else {
                reMap.put("result", df2.format(Double.parseDouble(
                        bdTop.divide(
                                bdSetSnp.add(bdAsSet).divide(tax, 4, BigDecimal.ROUND_HALF_EVEN),
                                4,
                                BigDecimal.ROUND_HALF_EVEN
                        ).subtract(new BigDecimal(1)).toString()))
                );
            }
            reList.add(reMap);
        }
        return reList;
    }

    /**
     * 获取GP
     */
    @Override
    public List<Map> getGP(String dealer, Integer quoNum) {
        报价单表Model qMo = qRep.findOne(quoNum);
        // 不能使用http请求中的许可，因为rcoo等集团用户登录时，会传入东二区等中文许可，应该使用报价单中的许可
        Lsh经销商表Model dealerModel = lsh经销商表Repository.findOne(qMo.get许可());
        Lsh车型Model carMo = carRep.findOne(qMo.get车型编号());
        List<Map> list = new ArrayList<>();
        Map<String, Object> paramQuoNum = Maps.newHashMap();
//        Map<String, Object> paramCity = Maps.newHashMap();
        Map<String, Object> paramBrand = Maps.newHashMap();
        Map<String, Object> paramPermit = Maps.newHashMap();
        paramQuoNum.put("quoNum", quoNum);//报价编号
//        paramCity.put("city", dealerModel.get城市()); // 城市
        paramBrand.put("brand", carMo.get品牌());
        paramPermit.put("permit", dealerModel.getBmbs());// 许可
        // GP1.5%（本单）
//        String sqlGp1d5Rate = "SELECT  \n" +
//                "	ifnull((  \n" +
//                "		A.`折后价` / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "		- case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end  \n" +
//                "		- ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
//                "		- ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
//                "		- ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)  \n" +
//                "	)  \n" +
//                "	/  \n" +
//                "	(  \n" +
//                "		A.`折后价` / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "	), 0)  * 100 gp1d5Rate  \n" +
//                "FROM 报价单表 A, LSH库存表 D  \n" +
//                "WHERE A.`库存编号` = D.`库存编号`  \n" +
//                "AND A.报价编号 = :quoNum  \n";
//        List data0 = this.getList(sqlGp1d5Rate, paramQuoNum);
//        Object obj0 = Iterables.getFirst(data0, null);
//        if (obj0 == null) {
//            Map<String, Object> map0 = Maps.newHashMap();
//            map0.put("gp1d5Rate", 0);
//            list.add(map0);
//        } else {
//            list.add((Map) data0.get(0));
//        }
        // GP1.5%(本市 过去一周)
//        String sqlGp1d5RateCity = "SELECT  \n" +
//                "	ifnull(sum(  \n" +
//                "		A.`折后价` / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "		- case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end  \n" +
//                "		- ifnull((SELECT SUM(IFNULL(随车成本, 0)) * 1.17 FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
//                "		- ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
//                "		- ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)  \n" +
//                "	)  \n" +
//                "	/  \n" +
//                "	sum(  \n" +
//                "		A.`折后价` / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "	), 0)  * 100 gp1d5RateCity  \n" +
//                "FROM 报价单表 A,LSH库存表 D, 报价交车表 E, 报价订单表 	F  \n" +
//                "WHERE A.`库存编号` = D.`库存编号` AND A.`交车` = E.`编号` AND  \n" +
//                "	  A.订单编号 = F.订单编号 AND  \n" +
//                "	  d.车辆零售状态 = '已卖' and  \n" +
//                "	  E.开票时间 > date_sub(current_date(), interval 7 day) AND  \n" +
//                "	  E.开票时间 < current_date() AND  \n" +
//                "     e.退票时间 is null and  \n" +
//                "	  A.许可 in (select 许可_bmbs FROM LSH经销商表 where 城市 = :city)    \n";
//        List data = this.getList(sqlGp1d5RateCity, paramCity);
//        Object obj = Iterables.getFirst(data, null);
//        if (obj == null) {
//            Map<String, Object> map2City = Maps.newHashMap();
//            map2City.put("gp1d5RateCity", 0);
//            list.add(map2City);
//        } else {
//            list.add((Map) data.get(0));
//        }
        // GP1.5%(本店 过去一周 平均)
//        String sqlGp1d5RateDealer = "SELECT  \n" +
//                "	ifnull(sum(  \n" +
//                "		A.`折后价` / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "		- case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end  \n" +
//                "		- ifnull((SELECT SUM(IFNULL(随车成本, 0)) * 1.17 FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
//                "		- ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
//                "		- ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)  \n" +
//                "	)  \n" +
//                "	/  \n" +
//                "	sum(  \n" +
//                "		A.`折后价` / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "	), 0)  * 100 gp1d5RateDealer  \n" +
//                "FROM 报价单表 A,LSH库存表 D, 报价交车表 E, 报价订单表 	F  \n" +
//                "WHERE A.`库存编号` = D.`库存编号` AND A.`交车` = E.`编号` AND  \n" +
//                "	  A.订单编号 = F.订单编号 AND  \n" +
//                "	  d.车辆零售状态 = '已卖' and  \n" +
//                "	  E.开票时间 > date_sub(current_date(), interval 7 day) AND  \n" +
//                "	  E.开票时间 < current_date() AND  \n" +
//                "     e.退票时间 is null and  \n" +
//                "	  A.许可 = :permit  \n";
//        List data2 = this.getList(sqlGp1d5RateDealer, paramPermit);
//        Object obj2 = Iterables.getFirst(data2, null);
//        if (obj2 == null) {
//            Map<String, Object> map2 = Maps.newHashMap();
//            map2.put("gp1d5RateDealer", 0);
//            list.add(map2);
//        } else {
//            list.add((Map) data2.get(0));
//        }

        List siList = getSi(quoNum);
        Double dReSi, dWhSi;
        if (siList.size() == 0) {
            dReSi = 0.0;
            dWhSi = 0.0;
        } else {
            try {
                dReSi = Double.parseDouble(((Map) siList.get(0)).get("reSi") + "");
                dWhSi = Double.parseDouble(((Map) siList.get(0)).get("whSi") + "");
            } catch (Exception e) {
                logger.error(String.format("计算预测GP3%%时出错，零售SI，批发SI转换成Double失败，报价编号%s, 零售SI%s，批发SI%s",
                        quoNum, ((Map) siList.get(0)).get("reSi"), ((Map) siList.get(0)).get("whSi")));
                dReSi = 0.0;
                dWhSi = 0.0;
            }
        }
        // GP3% 本单
//        String sqlGp3rate = "SELECT  \n" +
//                "	ifnull((  \n" +
//                "		A.`折后价` / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "       - case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end  \n" +
//                "       - ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
//                "		- ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
//                "       - ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)  \n" +
//                "       + " + dReSi + " + case when d.所属经销商 = d.调入经销商 then 0 else " + dWhSi + " end  " +
//                "	)  \n" +
//                "	/  \n" +
//                "	(  \n" +
//                "		ifnull(A.`折后价`, 0) / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "	), 0) * 100 as gp3Rate  \n" +
//                "FROM 报价单表 A  \n" +
//                "join LSH库存表 D on A.`库存编号` = D.`库存编号`  \n" +
//                "where A.报价编号 = :quoNum  \n";
//        List data3 = this.getList(sqlGp3rate, paramQuoNum);
//        Object obj3 = Iterables.getFirst(data3, null);
//        if (obj3 == null) {
//            Map<String, Object> map3 = Maps.newHashMap();
//            map3.put("gp3Rate", 0);
//            list.add(map3);
//        } else {
//            list.add((Map) data3.get(0));
//        }
        // GP3%(本市 过去一周)
//        String sqlGp3rateCity = "SELECT  \n" +
//                "	ifnull(  \n" +
//                "		sum(  \n" +
//                "			A.`折后价` / 1.17  \n" +
//                "			- ifnull(消费税, 0) / 1.17  \n" +
//                "		   - case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end  \n" +
//                "		   - ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
//                "			- ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
//                "		   - ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)  \n" +
//                "			+ (IFNULL(F.零售SI,'0')  \n" +
//                "			+ case when d.所属经销商 = d.调入经销商 then 0 else IFNULL(F.批发SI,'0') end ))  \n" +
//                "	  \n" +
//                "	/  \n" +
//                "	sum(  \n" +
//                "		ifnull(A.`折后价`, 0) / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "	), 0) * 100 gp3RateCity  \n" +
//                "FROM 报价单表 A,  \n" +
//                "	 LSH库存表 D LEFT JOIN 库存利润表 F ON F.库存编号 = D.库存编号,  \n" +
//                "	 报价交车表 E, 报价订单表 K  \n" +
//                "WHERE A.`库存编号` = D.`库存编号` AND A.`交车` = E.`编号` AND  \n" +
//                "A.订单编号 = K.订单编号 AND  \n" +
//                "E.开票时间 > date_sub(current_date(), interval 7 day) AND  \n" +
//                "E.开票时间 < current_date() AND  \n" +
//                "e.退票时间 is null and  \n" +
//                "A.许可 in (select 许可_bmbs FROM LSH经销商表 where 城市 = :city)  \n";
//        List data4 = this.getList(sqlGp3rateCity, paramCity);
//        Object obj4 = Iterables.getFirst(data4, null);
//        if (obj4 == null) {
//            Map<String, Object> map4 = Maps.newHashMap();
//            map4.put("gp3RateCity", 0);
//            list.add(map4);
//        } else {
//            list.add((Map) data4.get(0));
//        }
        // GP3%(本店 过去一周)
//        String sqlGp3rateDealer = "SELECT  \n" +
//                "	ifnull(  \n" +
//                "		sum(  \n" +
//                "			A.`折后价` / 1.17  \n" +
//                "			- ifnull(消费税, 0) / 1.17  \n" +
//                "		   - case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end  \n" +
//                "		   - ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
//                "			- ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
//                "		   - ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)  \n" +
//                "			+ (IFNULL(F.零售SI,'0')  \n" +
//                "			+ case when d.所属经销商 = d.调入经销商 then 0 else IFNULL(F.批发SI,'0') end ))  \n" +
//                "	  \n" +
//                "	/  \n" +
//                "	sum(  \n" +
//                "		ifnull(A.`折后价`, 0) / 1.17  \n" +
//                "		- ifnull(消费税, 0) / 1.17  \n" +
//                "	), 0) * 100 gp3RateDealer  \n" +
//                "FROM 报价单表 A,  \n" +
//                "	 LSH库存表 D LEFT JOIN 库存利润表 F ON F.库存编号 = D.库存编号,    \n" +
//                "	 报价交车表 E, 报价订单表 K    \n" +
//                "WHERE A.`库存编号` = D.`库存编号` AND A.`交车` = E.`编号` AND    \n" +
//                "A.订单编号 = K.订单编号 AND    \n" +
//                "E.开票时间 > date_sub(current_date(), interval 7 day) AND    \n" +
//                "E.开票时间 < current_date() AND    \n" +
//                "e.退票时间 is null and  \n" +
//                "A.许可 = :permit    \n";
//        List data5 = this.getList(sqlGp3rateDealer, paramPermit);
//        Object obj5 = Iterables.getFirst(data5, null);
//        if (obj5 == null) {
//            Map<String, Object> map5 = Maps.newHashMap();
//            map5.put("gp3RateDealer", 0);
//            list.add(map5);
//        } else {
//            list.add((Map) data5.get(0));
//        }

        // 本单GP4.5%
        String sqlTgpRate = "SELECT  \n" +
                "	ifnull((    \n" +
                "		A.`折后价` / 1.17    \n" +
                "		- ifnull(消费税, 0) / 1.17    \n" +
                "	   - case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end    \n" +
                "	   - ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)    \n" +
                "		- ifnull((select sum(成本) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)    \n" +
                "	   - ifnull((select sum(ifnull(厂家延保msrp, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 厂家延保实收 = 0), 0)    \n" +
                "	   - ifnull((select sum(ifnull(第三方延保msrp, 0)) / 1.17 from 报价五星延保 where 延保编号 = a.延保编号 and 第三方延保实收 = 0), 0)    \n" +
                "       + " + dReSi + " + case when d.所属经销商 = d.调入经销商 then 0 else " + dWhSi + " end  " +
                "	   + ifnull((SELECT (SUM(IFNULL(a.`折后价` * a.数量, 0)) - SUM(IFNULL(a.成本 * a.数量, 0))) / 1.17 AS 精品毛利 FROM 报价精品详细表 a where 编号 = a.精品 and 折后价 <> 0), 0)  \n" +
                "	   + ifnull((select 金融手续费 / 1.06 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 金融服务费 / 1.17 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 交强险手续费 / 1.06 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)   \n" +
                "	   + ifnull((select sum(手续费) / 1.06 from 报价保险详细表 where 保险编号 = a.保险编号 and 第N年 = 1 and 险种 <> '交强险' and 险种 <> '全车盗抢险'), 0)  \n" +
                "      + ifnull((select ifnull(手续费, 0) / 1.06 from 报价保险详细表 where 保险编号 = a.保险编号 and 第N年 = 1 and 险种 = '全车盗抢险'), 0)  \n" +
                "	   + ifnull((SELECT (IFNULL(厂家延保实收, 0) - IFNULL(厂家延保msrp, 0)) / 1.17 AS 厂家延保毛利 FROM 报价五星延保  WHERE 延保编号 = a.延保编号 and 厂家延保实收 > 0), 0)  \n" +
                "	   + ifnull((select (ifnull(第三方延保实收, 0) - ifnull(第三方延保msrp, 0)) / 1.17 as 第三方延保毛利 from 报价五星延保 where 延保编号 = a.延保编号 and 第三方延保实收 > 0), 0)  \n" +
                "      + ifnull((select IFNULL(置换手续费, 0) from 报价置换表 where 置换编号 = a.置换编号), 0) / 1.06  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费'), 0) / 1.17 - ifnull((select ifnull(费用原价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费成本'), 0) / 1.17  \n" +
                "      + ifnull((select ifnull(ifnull(其他一, 0) - ifnull(其他一成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他二, 0) - ifnull(其他二成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), 0) / 1.17  \n" +
                "      + ifnull((select ifnull(其他三, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他四, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他五, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "	)    \n" +
                "	/    \n" +
                "	(    \n" +
                "		ifnull(A.`折后价`, 0) / 1.17    \n" +
                "		- ifnull(消费税, 0) / 1.17   \n" +
                "		+ ifnull((SELECT SUM(IFNULL(a.`折后价`, 0)) / 1.17 AS 精品总价 FROM 报价精品详细表 a where 编号 = a.精品), 0)  \n" +
                "		+ ifnull((SELECT IFNULL(厂家延保实收, 0) / 1.17 AS 厂家延保销售价 FROM 报价五星延保  WHERE 延保编号 = a.延保编号), 0)  \n" +
                "       + ifnull((select ifnull(第三方延保实收, 0) / 1.17 as 第三方延保销售价 from 报价五星延保 where 延保编号 = a.延保编号), 0)  \n" +
                "	   + ifnull((select 金融手续费 / 1.06 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 交强险手续费 / 1.06 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)   \n" +
                "	   + ifnull((select sum(手续费) / 1.06 from 报价保险详细表 where 保险编号 = a.保险编号 and 第N年 = 1 and 险种 <> '交强险' and 险种 <> '全车盗抢险'), 0)  \n" +
                "	   + ifnull((select 金融服务费 / 1.17 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "      + ifnull((select ifnull(手续费, 0) from 报价保险详细表 where 保险编号 = a.保险编号 and 险种 = '全车盗抢险' and 第N年 = 1), 0) / 1.06  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费'), 0) / 1.17 - ifnull((select ifnull(费用原价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费成本'), 0) / 1.17  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), 0) / 1.17  \n" +
                "      + ifnull((select ifnull(ifnull(其他一, 0) - ifnull(其他一成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他二, 0) - ifnull(其他二成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他三, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他四, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他五, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "	), 0) * 100 as gp4d5Rate    \n" +
                "FROM 报价单表 A    \n" +
                "join LSH库存表 D on A.`库存编号` = D.`库存编号`    \n" +
                "where A.报价编号 = :quoNum  \n";
        List data6 = this.getList(sqlTgpRate, paramQuoNum);
        Object obj6 = Iterables.getFirst(data6, null);
        if (obj6 == null) {
            Map<String, Object> map6 = Maps.newHashMap();
            map6.put("gp4d5Rate", 0);
            list.add(map6);
        } else {
            list.add((Map) data6.get(0));
        }
        // GP4.5%（本品牌过去一周）
        String sqlTgpRateBrand = "SELECT    \n" +
                "	ifnull(avg((    \n" +
                "		A.`折后价` / 1.17    \n" +
                "		- ifnull(消费税, 0)    \n" +
                "	   - case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end    \n" +
                "	   - ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)    \n" +
                "		- ifnull((select sum(成本) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)    \n" +
                "	   - ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)    \n" +
                "       + " + dReSi + " + case when d.所属经销商 = d.调入经销商 then 0 else " + dWhSi + " end  " +
                "	   + ifnull((SELECT (SUM(IFNULL(a.`折后价` * a.数量, 0)) - SUM(IFNULL(a.成本 * a.数量, 0))) / 1.17 AS 精品毛利 FROM 报价精品详细表 a where 编号 = a.精品 and 折后价 <> 0), 0)  \n" +
                "	   + ifnull((select 金融手续费 / 1.17 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 金融服务费 / 1.06 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 交强险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)   \n" +
                "	   + ifnull((select 商业险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)  \n" +
                "      + ifnull((select ifnull(手续费, 0) from 报价保险详细表 where 保险编号 = a.保险编号 and 险种 = '全车盗抢险' and 第N年 = 1), 0)  \n" +
                "	   + ifnull((SELECT (IFNULL(延保折后价, 0) - IFNULL(延保原价, 0)) / 1.17 AS 延保毛利 FROM 报价五星延保  WHERE 延保编号 = a.延保编号 and 延保折后价 > 0), 0)  \n" +
                "      + ifnull((select IFNULL(置换手续费, 0) from 报价置换表 where 置换编号 = a.置换编号), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费'), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他一, 0) - ifnull(其他一成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他二, 0) - ifnull(其他二成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), 0)  \n" +
                "      + ifnull((select ifnull(其他三, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他四, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他五, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "	)    \n" +
                "	/    \n" +
                "	(    \n" +
                "		ifnull(A.`折后价`, 0) / 1.17    \n" +
                "		- ifnull(消费税, 0)   \n" +
                "		+ ifnull((SELECT SUM(IFNULL(a.`折后价`, 0)) / 1.17 AS 精品总价 FROM 报价精品详细表 a where 编号 = a.精品), 0)  \n" +
                "		+ ifnull((SELECT IFNULL(厂家延保实收, 0) / 1.17 AS 厂家延保销售价 FROM 报价五星延保  WHERE 延保编号 = a.延保编号), 0)  \n" +
                "       + ifnull((select ifnull(第三方延保实收, 0) / 1.17 as 第三方延保销售价 from 报价五星延保 where 延保编号 = a.延保编号), 0)  \n" +
                "	   + ifnull((select 金融手续费 / 1.17 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 交强险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)   \n" +
                "	   + ifnull((select 商业险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)  \n" +
                "	   + ifnull((select 金融服务费 / 1.06 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "      + ifnull((select ifnull(手续费, 0) from 报价保险详细表 where 保险编号 = a.保险编号 and 险种 = '全车盗抢险' and 第N年 = 1), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费'), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他一, 0) - ifnull(其他一成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他二, 0) - ifnull(其他二成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他三, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他四, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他五, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "	)), 0) * 100 as gp4d5RateMonth    \n" +
                "FROM 报价单表 A,      \n" +
                "	LSH库存表 D join lsh车型 f on d.车型编号 = f.车型编号,  \n" +
                "	报价交车表 E, 报价订单表 K      \n" +
                "WHERE A.`库存编号` = D.`库存编号` AND A.`交车` = E.`编号` AND  \n" +
                "A.订单编号 = K.订单编号 AND  \n" +
                "E.开票时间 > date_sub(current_date(), interval 7 day) AND  \n" +
                "E.开票时间 < current_date() AND  \n" +
                "e.退票时间 is null and  \n" +
                "f.品牌 = :brand  \n";
//        List data7 = this.getList(sqlTgpRateCity, paramCity);
        List data7 = this.getList(sqlTgpRateBrand, paramBrand);
        Object obj7 = Iterables.getFirst(data7, null);
        if (obj7 == null) {
            Map<String, Object> map7 = Maps.newHashMap();
            map7.put("gp4d5RateMonth", 0);
            list.add(map7);
        } else {
            list.add((Map) data7.get(0));
        }
        // GP4.5%（本店过去一周）
        String sqlTgpRateDealer = "SELECT    \n" +
                "	ifnull(avg((    \n" +
                "		A.`折后价` / 1.17    \n" +
                "		- ifnull(消费税, 0)    \n" +
                "	   - case when d.所属经销商 = d.调入经销商 then d.协商调车款项金额 / 1.17 else D.`采购价格未税` end    \n" +
                "	   - ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)    \n" +
                "		- ifnull((select sum(成本) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)    \n" +
                "	   - ifnull((select sum(ifnull(延保原价, 0)) / 1.17 FROM 报价五星延保 WHERE 延保编号 = a.延保编号 and 延保折后价 = 0 ), 0)    \n" +
                "       + " + dReSi + " + case when d.所属经销商 = d.调入经销商 then 0 else " + dWhSi + " end  " +
                "	   + ifnull((SELECT (SUM(IFNULL(a.`折后价` * a.数量, 0)) - SUM(IFNULL(a.成本 * a.数量, 0))) / 1.17 AS 精品毛利 FROM 报价精品详细表 a where 编号 = a.精品 and 折后价 <> 0), 0)  \n" +
                "	   + ifnull((select 金融手续费 / 1.17 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 金融服务费 / 1.06 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 交强险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)   \n" +
                "	   + ifnull((select 商业险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)  \n" +
                "      + ifnull((select ifnull(手续费, 0) from 报价保险详细表 where 保险编号 = a.保险编号 and 险种 = '全车盗抢险' and 第N年 = 1), 0)  \n" +
                "	   + ifnull((SELECT (IFNULL(延保折后价, 0) - IFNULL(延保原价, 0)) / 1.17 AS 延保毛利 FROM 报价五星延保  WHERE 延保编号 = a.延保编号 and 延保折后价 > 0), 0)  \n" +
                "      + ifnull((select IFNULL(置换手续费, 0) from 报价置换表 where 置换编号 = a.置换编号), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费'), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他一, 0) - ifnull(其他一成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他二, 0) - ifnull(其他二成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), 0)  \n" +
                "      + ifnull((select ifnull(其他三, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他四, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他五, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "	)    \n" +
                "	/    \n" +
                "	(    \n" +
                "		ifnull(A.`折后价`, 0) / 1.17    \n" +
                "		- ifnull(消费税, 0)   \n" +
                "		+ ifnull((SELECT SUM(IFNULL(a.`折后价`, 0)) / 1.17 AS 精品总价 FROM 报价精品详细表 a where 编号 = a.精品), 0)  \n" +
                "		+ ifnull((SELECT IFNULL(厂家延保实收, 0) / 1.17 AS 厂家延保销售价 FROM 报价五星延保  WHERE 延保编号 = a.延保编号), 0)  \n" +
                "       + ifnull((select ifnull(第三方延保实收, 0) / 1.17 as 第三方延保销售价 from 报价五星延保 where 延保编号 = a.延保编号), 0)  \n" +
                "	   + ifnull((select 金融手续费 / 1.17 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "	   + ifnull((select 交强险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)   \n" +
                "	   + ifnull((select 商业险手续费 / 1.17 from 报价保险表 where 保险编号 = a.保险编号 and 投保类型 = '新保'), 0)  \n" +
                "	   + ifnull((select 金融服务费 / 1.06 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "      + ifnull((select ifnull(手续费, 0) from 报价保险详细表 where 保险编号 = a.保险编号 and 险种 = '全车盗抢险' and 第N年 = 1), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '上牌服务费'), 0)  \n" +
                "      + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他一, 0) - ifnull(其他一成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(ifnull(其他二, 0) - ifnull(其他二成本, 0), 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他三, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他四, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "      + ifnull((select ifnull(其他五, 0) / 1.17 from 报价预收表 where 预收编号 = a.预收编号), 0)  \n" +
                "	)), 0) * 100 as gp4d5RateDealer    \n" +
                "FROM 报价单表 A,      \n" +
                "	LSH库存表 D,  \n" +
                "	报价交车表 E, 报价订单表 K      \n" +
                "WHERE A.`库存编号` = D.`库存编号` AND A.`交车` = E.`编号` AND  \n" +
                "A.订单编号 = K.订单编号 AND  \n" +
                "E.开票时间 > date_sub(current_date(), interval 7 day) AND  \n" +
                "E.开票时间 < current_date() AND  \n" +
                "e.退票时间 is null and  \n" +
                "A.许可 = :permit  \n";
        List data8 = this.getList(sqlTgpRateDealer, paramPermit);
        Object obj8 = Iterables.getFirst(data8, null);
        if (obj8 == null) {
            Map<String, Object> map8 = Maps.newHashMap();
            map8.put("gp4d5RateDealer", 0);
            list.add(map8);
        } else {
            list.add((Map) data8.get(0));
        }
        return list;
    }

    private List getSi(Integer qNum) {
        // 获取本单对应的预测批发SI与预测零售SI
        String siSql = "SELECT ifnull(BMBS_retail_SI, 0) reSi, ifnull(BMBS_wholesale_SI, 0) whSi  \n" +
                "FROM snp_setting a   \n" +
                "join 报价单表 b ON a.permit = b.`许可` and b.`报价编号` = :qNum   \n" +
                "WHERE a.carCode in (  \n" +
                "	select a.车型编号   \n" +
                "	from lsh车型 a   \n" +
                "	where EXISTS (  \n" +
                "		select *   \n" +
                "		from (  \n" +
                "			select b.来源, b.品牌, b.级别, b.车型, b.车型详细   \n" +
                "			from 报价单表 a   \n" +
                "			join lsh车型 b on a.车型编号 = b.车型编号   \n" +
                "			where a.报价编号 = :qNum  \n" +
                "		) b   \n" +
                "		where a.来源 = b.来源   \n" +
                "		and a.品牌 = b.品牌   \n" +
                "		and a.级别 = b.级别   \n" +
                "		and a.车型 = b.车型   \n" +
                "		and a.车型详细 = b.车型详细  \n" +
                "	)  \n" +
                ")   \n" +
                "and a.y_m = '190001'   \n" +
                "AND (  \n" +
                "	a.apperanceCode in (  \n" +
                "		select n.颜色代码   \n" +
                "		FROM 报价单表 m   \n" +
                "		JOIN lsh库存表 n on m.库存编号 = n.库存编号   \n" +
                "		where m.报价编号 = :qNum  \n" +
                "	) or a.apperanceCode = 0  \n" +
                ")   \n" +
                "ORDER BY a.apperanceCode DESC   \n" +
                "LIMIT 1   \n";
        Map<String, Object> params = new HashMap<>();
        params.put("qNum", qNum);
        return this.getList(siSql, params);
    }

    @Override
    public List getGp4d5(Integer qNum) {
        List siList = getSi(qNum);
        Double dReSi, dWhSi;
        if (siList.size() == 0) {
            dReSi = 0.0;
            dWhSi = 0.0;
        } else {
            try {
                dReSi = Double.parseDouble(((Map) siList.get(0)).get("reSi") + "");
                dWhSi = Double.parseDouble(((Map) siList.get(0)).get("whSi") + "");
            } catch (Exception e) {
                logger.error(String.format("计算预测GP3%%时出错，零售SI，批发SI转换成Double失败，报价编号%s, 零售SI%s，批发SI%s",
                        qNum, ((Map) siList.get(0)).get("reSi"), ((Map) siList.get(0)).get("whSi")));
                dReSi = 0.0;
                dWhSi = 0.0;
            }
        }
        String sql = "select  \n" +
                "  a.折后价 / 1.17  \n" +
                "  - ifnull(消费税, 0) / 1.17  \n" +
                "  - (select case when 所属经销商 = 调入经销商 then 协商调车款项金额 / 1.17 else 采购价格未税 end from lsh库存表 where 库存编号 = a.库存编号)  \n" +
                "  - ifnull((SELECT SUM(IFNULL(随车成本, 0)) FROM 报价随车表 WHERE 报价编号 = A.报价编号 and 随车类型 = '经销商' AND 随车部门='销售部'), 0)  \n" +
                "  - ifnull((select sum(成本 * 数量) / 1.17 from 报价精品详细表 where 编号 = a.精品 and 折后价 = 0 group by 编号), 0)  \n" +
                "  - ifnull((select sum(ifnull(厂家延保MSRP, 0)) / 1.17 from 报价五星延保 where 延保编号 = a.延保编号 and 厂家延保实收 = 0), 0)  \n" +
                "  - ifnull((select sum(ifnull(第三方延保MSRP, 0)) / 1.17 from 报价五星延保 where 延保编号 = a.延保编号 and 第三方延保实收 = 0), 0)  \n" +
                "  + " + dReSi + " + (select case when 所属经销商 = 调入经销商 then 0 else " + dWhSi + " end from lsh库存表 where 库存编号 = a.库存编号)  \n" +
                "  + ifnull((select sum((ifnull(折后价, 0) - ifnull(成本, 0)) * 数量) from 报价精品详细表 where 编号 = a.精品 and 折后价 > 0), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(金融手续费, 0) / 1.06 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "  + ifnull((select ifnull(金融服务费, 0) / 1.17 from 报价金融表 where 金融编号 = a.金融), 0)  \n" +
                "  + ifnull((select sum(手续费) from 报价保险详细表 where 保险编号 = a.保险编号 and 第N年 = 1), 0) / 1.06  \n" +
                "  + ifnull((select ifnull(厂家延保实收, 0) - ifnull(厂家延保MSRP, 0) from 报价五星延保 where 延保编号 = a.延保编号 and 厂家延保实收 > 0), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(第三方延保实收, 0) - ifnull(第三方延保MSRP, 0) from 报价五星延保 where 延保编号 = a.延保编号 and 第三方延保实收 > 0), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(置换手续费, 0) from 报价置换表 where 置换编号 = a.置换编号), 0) / 1.06  \n" +
                "  + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 报价费用详细表.费用名称='上牌服务费'), 0) / 1.17 - ifnull((select ifnull(费用原价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 报价费用详细表.费用名称 = '上牌服务费成本'), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(其他一, 0) - ifnull(其他一成本, 0) from 报价预收表 where 预收编号 = a.预收编号), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(其他二, 0) - ifnull(其他二成本, 0) from 报价预收表 where 预收编号 = a.预收编号), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(费用折后价, 0) from 报价费用详细表 where 费用编号 = a.费用编号 and 费用名称 = '其他服务费'), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(其他三, 0) from 报价预收表 where 预收编号 = a.预收编号), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(其他四, 0) from 报价预收表 where 预收编号 = a.预收编号), 0) / 1.17  \n" +
                "  + ifnull((select ifnull(其他五, 0) from 报价预收表 where 预收编号 = a.预收编号), 0) / 1.17  \n" +
                "  as gp4d5  \n" +
                "from 报价单表 a  \n" +
                "where a.报价编号 = :qNum  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("qNum", qNum);
        return this.getList(sql, params);
    }

    /**
     * 资源释放
     */
    @Override
    public PageData getRelease(int pageSize, int pageIndex, String permit) {
        String sql = "SELECT A.库存编号,B.报价编号,B.车型编号,C.人员姓名 AS 销售顾问,E.顾客姓名,E.电话号码 AS 顾客电话,D.订单审批通过日期,\n" +
                "ADDDATE(D.订单审批通过日期,INTERVAL IFNULL(D.资源释放期限,'0') DAY) AS 释放到期日期,D.订金金额, D.订单编号,\n" +
                "IF ((D.资源释放期限 is  null or D.资源释放期限 ='0'),'非延期','延期') AS 释放状态,\n" +
                "F.品牌,F.来源,F.级别, F.车型,F.车型详细 AS 意向车型,A.颜色代码 AS 车型外观编号\n" +
                "FROM( SELECT * FROM LSH库存表 WHERE 车辆零售状态 = '已卖' and  车架号 not like '%IRISTechnologies%') AS A\n" +
                "INNER JOIN 报价单表 AS B USING (报价编号)\n" +
                "INNER JOIN 顾客记录汇总 AS E ON B.顾客编号 = E.顾客编号\n" +
                "INNER JOIN (SELECT * FROM 用户  WHERE 许可 =:permit  ) AS C ON B.销售顾问 = C.用户名\n" +
                "INNER JOIN 报价订单表 AS D ON D.订单编号 = B.订单编号\n" +
                "INNER JOIN  LSH车型 F ON F.车型编号 = B.车型编号\n" +
                "WHERE D.订单审批通过日期 IS NOT NULL AND ADDDATE(D.订单审批通过日期,INTERVAL IFNULL(D.资源释放期限,'0') DAY)>= CURRENT_TIMESTAMP ()";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        return this.getPage(sql, params, pageSize, pageIndex);
    }

    /**
     * 查询需要进行资源释放的订单库存编号
     */
    @Override
    public List<Map> getInfo4ManualRelease(int quotedCode) {
        String sql = "SELECT DISTINCT t3.库存编号, t2.许可, t2.报价编号, t2.销售顾问,t4.车型详细, t3.车架号, t5.顾客姓名, t5.电话号码 "
                + " FROM 报价订单表 t1, 报价单表 t2, LSH库存表 t3,LSH车型 t4, 顾客记录汇总 t5"
                + " WHERE t2.库存编号= t3.库存编号 AND t2.订单编号=t1.订单编号 AND t3.车型编号=t4.车型编号 AND t2.顾客编号=t5.顾客编号"
                + " AND t2.报价编号 = :quotedCode";
        Map<String, Object> map = Maps.newHashMap();
        map.put("quotedCode", quotedCode);
        return this.getList(sql, map);
    }

    /**
     * 查询需要进行资源释放的订单库存编号
     */
    @Override
    public List<Map> getAutoRelease(String virtualStockPref) {
        String sql = "SELECT DISTINCT t3.库存编号, t2.许可, t2.报价编号, t2.销售顾问,t4.车型详细, t3.车架号, t5.顾客姓名"
                + " FROM 报价订单表 t1, 报价单表 t2, LSH库存表 t3,LSH车型 t4, 顾客记录汇总 t5"
                + " WHERE t2.库存编号= t3.库存编号 AND t2.订单编号=t1.订单编号 AND t3.车型编号=t4.车型编号 AND t2.顾客编号=t5.顾客编号"
                + " AND t3.车架号 not like '%" + virtualStockPref + "%' AND t3.车辆零售状态='已卖' AND t1.订单审批通过日期 IS NOT NULL AND t1.资源释放期限>0"
                + " AND DATE_ADD(t1.订单审批通过日期,INTERVAL (t1.资源释放期限+1) DAY)<=NOW()"
                + " AND (t1.订单状态  ='订单C' OR t1.订单状态  ='意向订单C' OR t1.订单状态  ='合同C')"
                + " ORDER BY t3.库存编号";

        return this.getList(sql, Maps.newHashMap());
    }

    /**
     * 查询需要进行资源释放的订单库存编号
     */
    @Override
    public List<Map> getAutoRelease4Notice(String virtualStockPref) {
        String sql = "SELECT DISTINCT t3.库存编号, t2.许可, t2.报价编号, t2.销售顾问 , t4.车型详细,  t3.车架号,  t5.顾客姓名 "
                + " FROM  报价订单表   t1,  lsh库存表   t3,  lsh车型   t4,  顾客记录汇总   t5, "
                + " 报价单表 t2 LEFT JOIN 销售项目设置 t8 ON t2.许可=t8.许可 AND t8.设置项目='资源释放周期'"
                + " WHERE t2.库存编号= t3.库存编号 AND t2.订单编号=t1.订单编号 AND t3.车型编号=t4.车型编号 AND t2.顾客编号=t5.顾客编号"
                + " AND t3.车架号 NOT LIKE '%" + virtualStockPref + "%' AND t3.车辆零售状态='已卖' AND t1.订单审批通过日期 IS NOT NULL AND t1.资源释放期限>0 "
                + " AND DATE_ADD(t1.订单审批通过日期,INTERVAL (t1.资源释放期限+1-IFNULL(t8.值,8)) DAY)<=NOW()"
                + " AND (t1.订单状态  ='订单C' OR t1.订单状态  ='意向订单C' OR t1.订单状态  ='合同C')"
                + " ORDER BY t3.库存编号";

        return this.getList(sql, Maps.newHashMap());
    }

    /**
     * 重复接待仲裁
     *
     * @param pageSize
     * @param pageIndex
     * @return
     */
    @Override
    public PageData getReArbitrate(int pageSize, int pageIndex, String permit) {
        //查询表"顾客待裁决"，如果有数据，根据电话，许可查找到"顾客记录汇总"，再根据顾客编号查询到进店记录
        String sql = " select " +
                "	顾客编号, " +
                "	许可, " +
                "	留档日期, " +
                "	意向车型A, " +
                "	顾客姓名, " +
                "	电话号码, " +
                "	新顾客级别, " +
                "	group_concat(销售顾问) as 销售顾问, " +
                "    group_concat(人员姓名) as 人员姓名, " +
                "	仲裁状态 " +
                "from ( " +
                "	SELECT distinct  " +
                "		a.顾客编号, " +
                "		a.许可, " +
                "		a.留档日期, " +
                "		c.车型详细 AS 意向车型A, " +
                "		a.顾客姓名, " +
                "		a.电话号码, " +
                "		a.新顾客级别, " +
                "		d.销售顾问, " +
                "		f.人员姓名, " +
                "		'未仲裁' AS 仲裁状态 " +
                "	FROM 顾客记录汇总 a " +
                "	JOIN 顾客待裁决 b ON a.电话号码 = b.电话号码 and a.许可 = b.许可 " +
                "	join 顾客进店记录表 d on a.顾客编号 = d.顾客编号 " +
                "	LEFT JOIN LSH车型 c ON a.意向车型A = c.车型编号 " +
                "	join 用户 f on d.销售顾问 = f.用户名 " +
                "	WHERE (a.裁决时间 IS NULL OR a.裁决时间 = '1910-11-11 11:11:11') " +
                "   and b.裁决时间 is null " +
                "	AND a.许可 = :permit " +
                "	and not exists (select b.顾客编号 from lsh导入名单 b WHERE 战败去向 IS NOT NULL AND 战败去向 <> '' and a.顾客编号 = b.顾客编号) " +
                ") aa group by 许可, 顾客编号 ";
        Map map = Maps.newHashMap();
        map.put("permit", permit);
        PageData p = this.getPage(sql, map, 10000, 0);
        String sql2 = "select a.顾客编号, " +
                "	a.许可, " +
                "    a.电话号码, " +
                "    a.销售顾问, " +
                "    a.裁决时间, " +
                "    b.人员姓名 " +
                "from 顾客记录汇总 a " +
                "join ( " +
                "	SELECT distinct   " +
                "		a.顾客编号,  " +
                "		a.许可,  " +
                "		a.顾客姓名,  " +
                "		a.电话号码,  " +
                "		d.销售顾问, " +
                "       f.人员姓名 " +
                "	FROM 顾客记录汇总 a  " +
                "	JOIN 顾客待裁决 b ON a.电话号码 = b.电话号码 and a.许可 = b.许可  " +
                "	join 顾客进店记录表 d on a.顾客编号 = d.顾客编号  " +
                "	LEFT JOIN LSH车型 c ON a.意向车型A = c.车型编号  " +
                "	join 用户 f on d.销售顾问 = f.用户名  " +
                "	WHERE (a.裁决时间 IS NULL OR a.裁决时间 = '1910-11-11 11:11:11') " +
                "    and b.裁决时间 is null " +
                "	AND a.许可 = :permit " +
                "	 and not exists (select b.顾客编号 from lsh导入名单 b WHERE 战败去向 IS NOT NULL AND 战败去向 <> '' and a.顾客编号 = b.顾客编号)  " +
                ") b on a.顾客编号 = b.顾客编号 and a.许可 = b.许可 and a.电话号码 = b.电话号码 and a.销售顾问 = b.销售顾问 " +
                "where a.裁决时间 is not null ";
        List arbitratedSales = getList(sql2, map);
        int size = p.getData().size();
        List listRecord = p.getData();
        Map mapRecord = new HashMap();

        // 消除已经仲裁过的，存在于顾客记录汇总中的数据，兼顾表关联取数
        for (Object a : listRecord) {
            Map record = (Map) a;
            String recordKey = (Integer) record.get("顾客编号") + (String) record.get("许可")
                    + (String) record.get("电话号码");
            mapRecord.put(recordKey, a);
        }
        for (Object arbitrated : arbitratedSales) {
            Map b = (Map) arbitrated;
            String recordKey = (Integer) b.get("顾客编号") + (String) b.get("许可")
                    + (String) b.get("电话号码");
            Map m = (Map) mapRecord.get(recordKey);
            // 将以逗号分隔的不需要的数据过滤掉
            m.put("销售顾问", ((String) m.get("销售顾问")).replace((String) b.get("销售顾问") + ",", ""));
            m.put("销售顾问", ((String) m.get("销售顾问")).replace("," + (String) b.get("销售顾问"), ""));
            m.put("人员姓名", ((String) m.get("人员姓名")).replace((String) b.get("人员姓名") + ",", ""));
            m.put("人员姓名", ((String) m.get("人员姓名")).replace("," + (String) b.get("人员姓名"), ""));
        }



      /*  // 消除已经仲裁过的，存在于顾客记录汇总中的数据，兼顾表关联取数
        for (int i = 0; i < size; i++) {
            for (int j = 0; j < arbitratedSales.size(); j++) {
                Map m = (Map) p.getData().get(i);
                Map b = (Map) arbitratedSales.get(j);
                if (((Integer) m.get("顾客编号")).equals((Integer) b.get("顾客编号"))
                        && ((String) m.get("许可")).equals((String) b.get("许可"))
                        && ((String) m.get("电话号码")).equals((String) b.get("电话号码"))
                        ) {
                    // 将以逗号分隔的不需要的数据过滤掉
                    m.put("销售顾问", ((String) m.get("销售顾问")).replace((String) b.get("销售顾问") + ",", ""));
                    m.put("销售顾问", ((String) m.get("销售顾问")).replace("," + (String) b.get("销售顾问"), ""));
                    m.put("人员姓名", ((String) m.get("人员姓名")).replace((String) b.get("人员姓名") + ",", ""));
                    m.put("人员姓名", ((String) m.get("人员姓名")).replace("," + (String) b.get("人员姓名"), ""));
                }
            }
        }*/
        for (int i = 0; i < size; i++) {
            Map m = (Map) p.getData().get(0);
            String[] sales = ((String) m.get("销售顾问")).split(",");
            String[] salesNames = ((String) m.get("人员姓名")).split(",");

            // 消除不需要的数据
            p.getData().remove(0);

            Map temp = null;
            // 少一次循环，将0放入记录第一表
            for (int j = 1; j < sales.length; j++) {
                temp = new HashMap();
                temp.putAll(m);
                temp.remove("销售顾问");
                temp.remove(("人员姓名"));
                temp.put("销售顾问一", sales[0]);
                temp.put("销售顾问姓名一", salesNames[0]);
                temp.put("销售顾问二", sales[j]);
                temp.put("销售顾问姓名二", salesNames[j]);
                p.getData().add(temp);
            }
        }
        return p;
    }

    public List getBaseReArbitrateTest(int noid) {
        StringBuilder sql = new StringBuilder();
        sql.append("           SELECT distinct   \n" +
                "                 a.顾客编号,  \n" +
                "                 a.许可,  \n" +
                "                 a.留档日期,  \n" +
                "                 c.车型 AS 意向车型A,  \n" +
                "                 a.顾客姓名,  \n" +
                "                 a.电话号码,  \n" +
                "                 a.新顾客级别,  \n" +
                "                 a.所属销售顾问  as 销售顾问一 ,  \n" +
                "                 if(f.人员姓名 is null,'',f.人员姓名)   as 销售顾问姓名一 ,  \n" +
                "                 '未仲裁' AS 仲裁状态  \n" +
                "                 FROM 顾客记录汇总 a  \n" +
                "                 LEFT JOIN LSH车型 c ON a.意向车型A = c.车型编号  \n" +
                "                  left join 用户 f on a.所属销售顾问 = f.用户名  \n" +
                "                 WHERE  a.顾客编号 =:noid     ");
        Map param = new HashMap();
        param.put("noid", noid);
        return this.getList(sql.toString(), param);

    }

    public List getNotReArbitrateTest(String permit) {
        StringBuilder sql = new StringBuilder();
        sql.append("   select  DISTINCT  a.电话号码   " +
                "      from \n" +
                " `顾客待裁决` a     \n" +
                "where a.裁决时间 is null   \n" +
                "and a.许可=:permit     ");
        Map param = new HashMap();
        param.put("permit", permit);
        return this.getList(sql.toString(), param);
    }


    @Override
    public PageData getSaleAdminCheckList(String permit, String username, ApprovalService.GetSaleAdminCheckList getSaleAdminCheckList) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select distinct a.id,  ");
        sb.append(" 	f.生产号,  ");
        sb.append(" 	f.车架号 as 车架号（VIN）,  ");
        sb.append(" 	f.库龄天数,  ");
        sb.append(" 	g.`订单签署日期`, ");
        sb.append(" 	h.合同签约日期,      ");
        sb.append(" 	b.车型编号,   ");
        sb.append(" 	b.品牌 as 品牌,   ");
        sb.append(" 	b.来源,   ");
        sb.append(" 	b.级别 as 车系,   ");
        sb.append(" 	b.车型 as 车型,   ");
        sb.append(" 	b.车型详细 as 车款, ");
        sb.append(" 	`用户`.`人员姓名` 销售顾问, ");
        sb.append(" 	e.顾客姓名 as 订单客户,   ");
        sb.append(" 	e.电话号码 as 订单客户电话,   ");
        sb.append(" 	d.车辆销售价 as 车辆折后价,   ");
        sb.append(" 	f.颜色,   ");
        sb.append(" 	f.内饰,   ");
        sb.append(" 	a.transcation_code,   ");
        sb.append(" 	c.quotedPriceCodeOrVIN,   ");
        sb.append(" 	f.NBI车辆状态,   ");
        sb.append(" 	a.check_status as 状态,   ");
        sb.append("     IF(`采购批发商`.`经销商名` IS NULL, f.采购批发商, `采购批发商`.`经销商名`) `采购经销商`, ");
        sb.append("     f.remark as 备注,f.route_days as  在途天数, ");
        sb.append(" TO_DAYS(DATE_FORMAT(IF(h.开票时间 is null ,CURDATE(),h.开票时间),'%Y%m%d')) - TO_DAYS(IFNULL(exchange_stock_log.update_time,g.`订单申请日期`)) 配车天数,IFNULL(exchange_stock_log.update_time,g.`订单申请日期`)  配车日期, ");
        sb.append(" IF(ISNULL(ccc.id),'否','是') AS '是否预装' ");
        sb.append(" from sale_admin_car_resource_check a   ");
        sb.append(" join LSH车型 b on a.car_code = b.车型编号   ");
        sb.append(" join pushApprovalWorkFlow c on a.transcation_code = c.transcation_code   ");
        sb.append(" join 报价单表 d on a.quotedPriceCode = d.报价编号   ");
        sb.append(" join 顾客记录汇总 e on d.顾客编号 = e.顾客编号   ");
        sb.append(" join LSH库存表 f on d.库存编号 = f.库存编号  ");
        sb.append(" join  报价订单表 g on  d.订单编号=g.订单编号   ");
        sb.append(" join  报价交车表 h on d.交车=h.编号     ");
        sb.append(" JOIN `用户` ON `用户`.`用户名` = d.`销售顾问` ");
        sb.append(" JOIN `LSH经销商表` `采购批发商` ON 采购批发商.`许可_BMBS` = f.`采购批发商` " +
                " LEFT JOIN  (select org_qid,MAX(update_time) update_time from exchange_stock_log group by org_qid ) exchange_stock_log ON exchange_stock_log.org_qid = d.`初始报价单号` ");
        sb.append(" LEFT JOIN custom_car_configure ccc ON f.库存编号=ccc.store_id AND ccc.`status`=1 ");
        sb.append(" where d.许可 = :permit  ");
        sb.append(" and a.check_status = '00' ");
        Map<String, Object> params = Maps.newHashMap();
        params.put("permit", permit);
        if (getSaleAdminCheckList.orderSignatureDateStart != null && !"".equals(getSaleAdminCheckList.orderSignatureDateStart)) {
            sb.append(" AND g.`订单签署日期` >= :orderSignatureDateStart ");
            params.put("orderSignatureDateStart", getSaleAdminCheckList.orderSignatureDateStart);
        }
        if (getSaleAdminCheckList.orderSignatureDateEnd != null && !"".equals(getSaleAdminCheckList.orderSignatureDateEnd)) {
            sb.append(" AND g.`订单签署日期` <= :orderSignatureDateEnd ");
            params.put("orderSignatureDateEnd", getSaleAdminCheckList.orderSignatureDateEnd);
        }
        if (getSaleAdminCheckList.contractSignatureDateStart != null && !"".equals(getSaleAdminCheckList.contractSignatureDateStart)) {
            sb.append(" AND h.合同签约日期 >= :contractSignatureDateStart ");
            params.put("contractSignatureDateStart", getSaleAdminCheckList.contractSignatureDateStart);
        }
        if (getSaleAdminCheckList.contractSignatureDateEnd != null && !"".equals(getSaleAdminCheckList.contractSignatureDateEnd)) {
            sb.append(" AND h.合同签约日期 <= :contractSignatureDateEnd ");
            params.put("contractSignatureDateEnd", getSaleAdminCheckList.contractSignatureDateEnd);
        }
        if (getSaleAdminCheckList.scName != null && !"".equals(getSaleAdminCheckList.scName)) {
            sb.append(" AND `用户`.`人员姓名` LIKE :scName ");
            params.put("scName", "%" + getSaleAdminCheckList.scName + "%");
        }
        if (getSaleAdminCheckList.cusName != null && !"".equals(getSaleAdminCheckList.cusName)) {
            sb.append(" AND e.顾客姓名 LIKE :cusName ");
            params.put("cusName", "%" + getSaleAdminCheckList.cusName + "%");
        }
        sb.append(" order by a.id desc ");
        return getPage(sb.toString(), params, getSaleAdminCheckList.getPageSize(), getSaleAdminCheckList.getPageIndex());
    }

    @Override
    public Map postReArbitrate(ApprovalService.UpdateReArbitrateDetail req) {
        String sql1 = "select count(1) " +
                "from ( " +
                "	select distinct 许可, 顾客姓名, 电话号码, 销售顾问 " +
                "	from 顾客待裁决 " +
                "	where 许可 = :permit " +
                "	and 电话号码 = :cusTel " +
                "	and 裁决时间 is null " +
                ") a ";
        String sql2 = "select count(1) " +
                "from ( " +
                "	select distinct 顾客编号, 许可, 顾客姓名, 电话号码, 销售顾问 " +
                "	from 顾客记录汇总 " +
                "	where 顾客编号 = :cusId " +
                "   and 许可 = :permit " +
                "	and 电话号码 = :cusTel " +
                "	and 裁决时间 is null " +
                ") a ";
        Map params = Maps.newHashMap();
        params.put("permit", req.permit);
        params.put("cusTel", req.cusTel);
        BigInteger count1 = (BigInteger) ((Map) getList(sql1, params).get(0)).get("count(1)");
        params.put("cusId", req.cusId);
        BigInteger count2 = (BigInteger) ((Map) getList(sql2, params).get(0)).get("count(1)");
        String sql3 = "	select distinct 许可, 顾客姓名, 电话号码, 销售顾问 " +
                "	from 顾客待裁决 " +
                "	where 许可 = :permit " +
                "	and 电话号码 = :cusTel " +
                "	and 裁决时间 is null ";
        String sql4 = "	select distinct 顾客编号, 许可, 顾客姓名, 电话号码, 销售顾问 " +
                "	from 顾客记录汇总 " +
                "	where 顾客编号 = :cusId " +
                "   and 许可 = :permit " +
                "	and 电话号码 = :cusTel " +
                "	and 裁决时间 is null ";
        params.remove("cusId");
        List reA = getList(sql3, params);
        params.put("cusId", req.cusId);
        List reB = getList(sql4, params);
        Map reMap = Maps.newHashMap();
        if (count1.equals(new BigInteger("1")) && count2.equals(new BigInteger("0"))) {
            reMap.put("modify 顾客待裁决", reA);
            return reMap;
        } else if (count1.equals(new BigInteger("0")) && count2.equals(new BigInteger("1"))) {
            reMap.put("modify 顾客记录汇总", reB);
            return reMap;
        } else {
            return null;
        }
    }

    @Override
    public int updateReArbitrateDetail(ApprovalService.UpdateReArbitrateDetail req) {
        try {
            String sql = "select 1 " +
                    "from 顾客待裁决 " +
                    "where 许可 = :permit " +
                    "and 顾客姓名 = :cusName " +
                    "and 销售顾问 = :saleMan " +
                    "and 电话号码 = :cusTel " +
                    "and 裁决时间 is null ";
            Map params = Maps.newHashMap();
            params.put("permit", req.permit);
            params.put("cusName", req.cusName);
            params.put("saleMan", req.saleMan);
            params.put("cusTel", req.cusTel);
            if (getList(sql, params).size() == 0) {
                return 0;
            }
            String sql2 = "select 1 " +
                    "from 顾客记录汇总 " +
                    "where 顾客编号 = :cusId " +
                    "and 许可 = :permit " +
                    "and 顾客姓名 = :cusName " +
                    "and 销售顾问 = :saleMan " +
                    "and 电话号码 = :cusTel " +
                    "and 裁决时间 is null ";
            params.put("cusId", req.cusId);
            if (getList(sql2, params).size() == 0) {
                return 1;
            }
            return -1;
        } catch (Exception e) {
            logger.error("数据更新异常。", e);
            return -1;
        }
    }

    /**
     * 查询仲裁详情
     *
     * @param saleManA
     * @param saleManB
     * @param id
     * @return
     */
    @Override
    public Map getReArbitrateDetail(String permit, String saleManA, String saleManB, String id) {
//        String sqlA = "SELECT 进店日期 FROM 顾客进店记录表 WHERE 顾客编号=:id AND 销售顾问=:saleMan";
        String sqlA = "select distinct d.进店日期 " +
                "FROM 顾客记录汇总 a " +
                "JOIN 顾客待裁决 b ON a.电话号码 = b.电话号码 and a.许可 = b.许可 " +
                "join 顾客进店记录表 d on a.顾客编号 = d.顾客编号 " +
                "WHERE (b.裁决时间 IS NULL OR b.裁决时间 = '0000-00-00 00:00:00') " +
                "AND a.许可 = :permit " +
                "and a.顾客编号 = :cusCode " +
                "and d.销售顾问 = :sc " +
                "and a.顾客编号 not in (select ifnull(顾客编号, 0) from lsh导入名单 WHERE (战败去向 IS NOT NULL AND 战败去向 <> '')) ";
        Map paramsA = Maps.newHashMap();
        paramsA.put("permit", permit);
        paramsA.put("sc", saleManA);
        paramsA.put("cusCode", id);
        List listA = this.getList(sqlA, paramsA);

        paramsA.put("sc", saleManB);

        List listB = this.getList(sqlA, paramsA);

        Map<String, Object> map = new HashMap<>();
        map.put("listA", listA);
        map.put("listB", listB);
        return map;
    }

    @Override
    public List showCarTransferLog(String permit, String VIN) {
        StringBuilder sql = new StringBuilder();
        sql.append("select " +
                "	a.申请销售顾问, " +
                "    b.custom_name, " +
                "    b.new_car_sale_status, " +
                "    a.调车理由, " +
                "    d.预计开票日期 as 客户预计购车日期, " +
                "    a.生产号, " +
                "    a.车架号, " +
                "    a.调车物流状态, " +
                "    a.预计到店日期, " +
                "    a.调出经销商, " +
                "    e.车型详细, " +
                "    a.加配配置, " +
                "    a.采购价格含税, " +
                "    a.实际MSRP含税, " +
                "    a.库龄天数, " +
                "    a.协商调车款项金额, " +
                "    b.internal_apply_date, " +
                "	b.internal_apply_user, " +
                "	b.internal_approval_date, " +
                "	b.internal_approval_user, " +
                "	b.external_apply_date, " +
                "	b.external_apply_user, " +
                "	b.out_confirm_date, " +
                "	b.out_confirm_user, " +
                "	b.invoice_issuing_date, " +
                "	b.invoice_issuing_user, " +
                "	b.fund_out_date, " +
                "	b.fund_out_user, " +
                "	b.fund_in_date, " +
                "	b.fund_in_user, " +
                "	b.stock_out_date, " +
                "	b.stock_out_user, " +
                "	b.stock_in_date, " +
                "	b.stock_in_user " +
                "from LSH库存表 a " +
                "join car_transfer_log b on a.车架号 = b.VIN " +
                "join 报价单表 c on a.库存编号 = c.库存编号 " +
                "join 报价订单表 d on c.订单编号 = d.订单编号 " +
                "join LSH车型 e on a.车型编号 = e.车型编号 " +
                "join salesAdmin f on a.车架号 = f.VIN " +
                "where 1 = 1 "
        );
        Map<String, String> params = new HashMap<>();
        if (!Strings.isNullOrEmpty(permit)) {
            sql.append("and c.许可 = :permit ");
            params.put("permit", permit);
        }
        if (!Strings.isNullOrEmpty(VIN)) {
            sql.append("and a.车架号 = :VIN ");
            params.put("VIN", VIN);
        }
        return this.getList(sql.toString(), params);
    }


    // 拿到 上一次的 编号
    public List<Integer> findLastBianhaoDetail(int ids) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  " +
                "       b.精品 as 精品  " +
                "  FROM  " +
                "       `报价单表` b  " +
                "   WHERE   b.`是否还原点`='是'  and    " +
                "       b.`初始报价单号` = (    " +
                "              SELECT  " +
                "                     `初始报价单号`  " +
                "              FROM  " +
                "                     `报价单表`  " +
                "              WHERE  " +
                "                     `报价编号` = :ids  " +
                "       )  " +
                "   ORDER BY  " +
                "       `报价编号` DESC    " +
                "   LIMIT 1,1   ");

        Map paramsA = Maps.newHashMap();
        paramsA.put("ids", ids);
        return this.getList(sql.toString(), paramsA);

    }


    // 这是 拿到 新增的 报价编号 对应的 所有的 _精品的 List
    public List findValueModelList(int ids) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT   e.精品编号 as productId,   " +
                "            c.编号,     " +
                "            c.ID as comboId,   " +
                "            a.报价编号,   " +
                "            c.数量,   " +
                "            c.折后价 as 折后价,  " +
                "            a.精品, " +
                "            a.初始报价单号     " +
                "    FROM    报价单表 a, 报价精品详细表 c, LSH精品套餐详细 d, LSH精品信息 e   " +
                "    where a.报价编号=:ids  and c.编号 = a.精品 and c.ID = d.套餐编号 and d.精品编号 = e.精品编号 ");

        Map paramsA = Maps.newHashMap();
        paramsA.put("ids", ids);
        return this.getList(sql.toString(), paramsA);
    }

    // 这是 拿到 新增的 报价编号 对应的 所有的 _精品的 List
    public List findValueModelListis(int ids) {
        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT " +
                "  e.精品编号  " +
                "  FROM " +
                "  报价单表 a, 报价精品详细表 c , LSH精品套餐详细 d , LSH精品信息 e   " +
                "  where a.报价编号=:ids and c.`编号` = a.`精品` and c.ID = d.套餐编号 and d.精品编号 = e.精品编号");

        Map paramsA = Maps.newHashMap();
        paramsA.put("ids", ids);
        return this.getList(sql.toString(), paramsA);
    }


    // 根据 精品 得到 所有 需要的 东西
    public List findLastModelListis(String ids) {
        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT   e.精品编号 as productId,  " +
                "    c.编号,  " +
                "    c.ID as comboId,   " +
                "    a.报价编号,   " +
                "    c.数量 ,  " +
                "    c.折后价 ,  " +
                "    a.精品,    " +
                "    a.初始报价单号   " +
                "            FROM    " +
                "    报价单表 a    " +
                "    LEFT JOIN 报价精品详细表 c ON c.编号 = a.精品   " +
                "    LEFT JOIN LSH精品套餐详细 d ON c.ID = d.套餐编号   " +
                "    LEFT JOIN LSH精品信息 e ON d.精品编号 = e.精品编号   " +
                "    where a.精品=:ids ");

        Map paramsA = Maps.newHashMap();
        paramsA.put("ids", ids);
        return this.getList(sql.toString(), paramsA);
    }


    public List findLastBianhao(int ids) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  " +
                "       b.报价编号  " +
                "   FROM  " +
                "       `报价单表` b  " +
                "WHERE   b.`是否还原点`='是'  and    " +
                "       b.`初始报价单号` = (  " +
                "              SELECT  " +
                "                     `初始报价单号`  " +
                "              FROM  " +
                "                     `报价单表`  " +
                "              WHERE  " +
                "                     `报价编号` = :ids  " +
                "       )  " +
                "ORDER BY  " +
                "       `报价编号` DESC  " +
                "LIMIT 1,1");

        Map paramsA = Maps.newHashMap();
        paramsA.put("ids", ids);
        return this.getList(sql.toString(), paramsA);


    }

    // 20161130
    // 根据朱思捷的意思，有效的报价编号，是与传入的报价编号，具有相同的日期与初始报价编号的所有记录中，报价编号最大的一个(过滤驳回)
    // 根据传入的报价单号，拿到关联客户进店记录表的有效的报价编号
    @Override
    public Integer getValidQuotedPriceCode(Integer quotedPriceCode) {
        String sql = "select max(报价编号)    " +
                "from 报价单表    " +
                "where substr(报价时间, 1, 10) =  (select substr(报价时间, 1, 10) from 报价单表 where 报价编号 = :quotedPriceCode)    " +
                "and 初始报价单号 = (select 初始报价单号 from 报价单表 where 报价编号 = :quotedPriceCode)    " +
                "and 是否驳回 = '否'    ";
        Map<String, Object> params = Maps.newHashMap();
        params.put("quotedPriceCode", quotedPriceCode);
        return (Integer) ((Map) getList(sql, params).get(0)).get("max(报价编号)");
    }


    public String getBugAndLshItask(String chushibaojia) {
        String restr = "0";
        String sql = "SELECT  a.`报价编号` as id from \n" +
                " 报价单表 a   \n" +
                "where a.`初始报价单号`=:chushibaojia  \n" +
                "  and  a.`是否驳回`='否'   \n" +
                "ORDER BY  a.`报价编号` DESC LIMIT 1  ";
        Map params = Maps.newHashMap();
        params.put("chushibaojia", chushibaojia);
        List nimadelist = this.getList(sql, params);
        if (nimadelist != null && nimadelist.size() > 0) {
            Map resulMap = new HashMap();
            resulMap = (Map) (nimadelist.get(0));
            restr = resulMap.get("id").toString();
        } else {
            restr = "0";
        }
        return restr;

    }

    @Override
    public List findNotRepeatReArbByPTel(String permit, String tel, String scman, String cid, String failMan) {
        String sql = "select * from ( select a.`销售顾问` as scMan,  \n" +
                "COUNT(DISTINCT a.`销售顾问`) as sum \n" +
                " from   顾客待裁决 a \n" +
                "where  \n" +
                "a.许可=:permit AND a.电话号码=:tel  \n" +
                "and a.`销售顾问`<>:sucessMan   \n" +
                "GROUP BY a.`销售顾问`  ) a where a.scMan=:failMan ";
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("tel", tel);
        params.put("sucessMan", scman);
        params.put("failMan", failMan);
        return this.getList(sql, params);
    }

    @Override
    public List getAfterMarkOrderList(String oid) {
        String sql = " select  a.id  from 报价精品详细表_精品 a " +
                "  join 报价单表 b on a.编号=b.精品 " +
                " where  b.初始报价单号=:oid    ";
        Map params = Maps.newHashMap();
        params.put("oid", oid);
        return this.getList(sql, params);
    }

    @Override
    public PageData reviewContractApproval(ApprovalService.ReviewContractApprovalRequest req, String permit) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  \n" +
                "  id,  \n" +
                "  permit,  \n" +
                "  scName,  \n" +
                "  invoiceUsername,  \n" +
                "  leadCell,  \n" +
                "  applyDate,  \n" +
                "  carModel,  \n" +
                "  approvedDate,  \n" +
                "  approvalType  \n" +
                "from (  \n" +
                "  select  \n" +
                "    a.*,  \n" +
                "    (select bb.人员姓名 from 报价单表 aa, 用户 bb where aa.报价编号 = a.quotedPriceCodeOrVIN and aa.销售顾问 = bb.用户名) as scName,  \n" +
                "    (select bb.开票客户姓名 from 报价单表 aa, 报价交车表 bb where aa.报价编号 = a.quotedPriceCodeOrVIN and aa.交车 = bb.编号) as invoiceUsername,  \n" +
                "    (select bb.电话号码 from 报价单表 aa, 顾客记录汇总 bb where aa.报价编号 = a.quotedPriceCodeOrVIN and aa.顾客编号 = bb.顾客编号) as leadCell,  \n" +
                "    left(right(a.transcation_code, 19), 10) as applyDate,  \n" +
                "    (select bb.车型详细 from 报价单表 aa, lsh车型 bb where aa.报价编号 = a.quotedPriceCodeOrVIN and aa.车型编号 = bb.车型编号) as carModel,  \n" +
                "    left(a.processTime, 10) as approvedDate,  \n" +
                "    case a.type  \n" +
                "      when 3 then '合同审批'  \n" +
                "      when 10 then '合同修改'  \n" +
                "    end as approvalType  \n" +
                "  from pushapprovalworkflow a  \n" +
                "  join (  \n" +
                "    select max(id) as mStepId  \n" +
                "    from pushapprovalworkflow  \n" +
                "    where type in (3, 10)  \n" +
                "    group by transcation_code  \n" +
                "  ) b on a.id = b.mStepId  \n" +
                "  where a.approvalStatus in (1, 3)  \n" +
                ") c where permit = :permit  \n");
        Map<String, Object> params = new HashMap<>();
        params.put("permit", permit);
        if (req.applyStartDate != null && req.applyStartDate.length() != 0) {
            sb.append("and date_format(:applyStartDate, '%Y-%m-%d') <= date_format(applyDate, '%Y-%m-%d')  \n");
            params.put("applyStartDate", req.applyStartDate);
        }
        if (req.applyEndDate != null && req.applyEndDate.length() != 0) {
            sb.append("and date_format(applyDate, '%Y-%m-%d') <= date_format(:applyEndDate, '%Y-%m-%d')  \n");
            params.put("applyEndDate", req.applyEndDate);
        }
        if (req.approvedStartDate != null && req.approvedStartDate.length() != 0) {
            sb.append("and date_format(:approvedStartDate, '%Y-%m-%d') <= date_format(approvedDate, '%Y-%m-%d')  \n");
            params.put("approvedStartDate", req.approvedStartDate);
        }
        if (req.approvedEndDate != null && req.approvedEndDate.length() != 0) {
            sb.append("and date_format(approvedDate, '%Y-%m-%d') <= date_format(:approvedEndDate, '%Y-%m-%d')  \n");
            params.put("approvedEndDate", req.approvedEndDate);
        }
        if (req.scName != null && req.scName.length() != 0) {
            sb.append("and scName like :scName  \n");
            params.put("scName", "%" + req.scName + "%");
        }
        if (req.invoiceUsername != null && req.invoiceUsername.length() != 0) {
            sb.append("and invoiceUsername like :invoiceUsername  \n");
            params.put("invoiceUsername", "%" + req.invoiceUsername + "%");
        }
        if (req.approvalType != null && req.approvalType.length() != 0) {
            sb.append("and approvalType = :approvalType  \n");
            params.put("approvalType", req.approvalType);
        }
        return this.getPage(sb.toString(), params, req.getPageSize(), req.getPageIndex());
    }

    @Override
    public List reviewContractApprovalDetail(Integer id) {
        String sql = "select  \n" +
                "  transcation_code,  \n" +
                "  processTime,  \n" +
                "  case step  \n" +
                "    when 'step1' then '一级审批'  \n" +
                "    when 'step2' then '二级审批'  \n" +
                "    when 'step3' then '三级审批'  \n" +
                "    when 'step4' then '四级审批'  \n" +
                "    when 'step5' then '五级审批'  \n" +
                "    when 'step6' then '六级审批'  \n" +
                "    when 'step7' then '七级审批'  \n" +
                "    when 'step8' then '八级审批'  \n" +
                "    when 'step9' then '九级审批'  \n" +
                "    else '未知级别'  \n" +
                "  end as step,  \n" +
                "  (select 角色 from 用户 where 用户名 = username) as role,  \n" +
                "  (select 人员姓名 from 用户 where 用户名 = username) as username,  \n" +
                "  case approvalStatus  \n" +
                "    when 1 then '通过'  \n" +
                "    when 3 then '自动通过'" +
                "   when 9 then '自动通过' \n" +
                "    else '未知状态'  \n" +
                "  end as approvalStatus  \n" +
                "from pushapprovalworkflow  \n" +
                "where id between :startId and :endId  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("startId", id - 10 <= 0 ? 0 : id - 10);
        params.put("endId", id);
        return this.getList(sql, params);
    }

    @Override
    public PageData FinanceDepositCheckList(SetService.inOutAccountRequest re, String permit) {
        Map<String, String> params = Maps.newHashMap();
        params.put("permit",permit);
        PageData pg;
        StringBuilder sql =   new StringBuilder("SELECT  \n" +
                "    a.sum1 as 金额,    \n" +
                "   check_status as 状态, " +
                "   a.*," +
                "   count(a.报价编号) 报价数   \n" +
                "   from (  \n" +
                "        SELECT DISTINCT     \n" +
                "           if(n.`新车销售状态` ='12','意向金','订金') 付款目的,    \n" +
                "               fd.deposit_money as sum1 ,  \n" +
                "           if((select g.实付金额 from `报价付款详细` g where g.付款事由 = if(n.`新车销售状态` = 12,'意向金','订金') and g.报价编号= d.`报价编号`) is null,  \n" +
                "               null, (select g.应付金额 from `报价付款详细` g where g.付款事由 = if(n.`新车销售状态` = 12,'意向金','订金') and g.报价编号= d.`报价编号`)) as sum2 ,  \n" +
                "    b.`车型` 意向车型," +
                "    k.用户名 sc_name, " +
                "    fd.permit," +
                "    fd.order_type,\n" +
                "    b.`车型详细` 意向车款,      \n" +
                "    if(locate('IRIS',a.`车架号`) > 0,'',a.`车架号`) as`车架号(VIN)`,       \n" +
                "    b.`品牌`,  \n" +
                "    b.`级别`,  \n" +
                "    b.`车型`,  \n" +
                "    b.`车型详细`,     \n" +
                "    l.`订单客户姓名` 客户名称,    \n" +
                "    N .`电话号码` 客户电话,    \n" +
                "    k.`人员姓名` 销售顾问," +
                "    (case when fd.check_status = '01' then  '已确认' when check_status = '00' then '待确认' else null END)  check_status,   \n" +
                "        CASE\n" +
                "      WHEN NOT ISNULL(bjjc.`交车日期`) THEN '18'\n" +
                "      WHEN (NOT ISNULL(bjjc.`开票时间`) AND ISNULL(bjjc.`退票时间`))\n" +
                "\tOR (NOT ISNULL(bjjc.`开票时间`) AND NOT ISNULL(bjjc.`退票时间`) AND bjjc.`开票时间`>bjjc.`退票时间`)\n" +
                "\t  THEN '17'\n" +
                "      WHEN NOT ISNULL(bjjc.`退票时间`) AND l.`订金类型`='订金' THEN '13'\n" +
                "      WHEN NOT ISNULL(l.`订单失销日期`) AND (l.`订单状态`='退订C') THEN '8'\n" +
                "      WHEN NOT ISNULL(e.`应付金额`) AND NOT ISNULL(e.`实付金额`) AND ROUND(e.`实付金额`,4)>=ROUND(e.`应付金额`,4) and e.实付金额 > 0 THEN '15'\n" +
                "      WHEN NOT ISNULL(bjjc.`合同签约日期`) THEN '16'\n" +
                "      when not isnull(l.`订单审批通过日期`) and date(l.`订单审批通过日期`) >= ifnull(l.`订单失销日期`,'1970-01-01') AND l.`订金类型`='订金' and l.订单状态 <> '退订C' and l.订单状态 is not null THEN '13'\n" +
                "      when not isnull(l.`订单审批通过日期`) and date(l.`订单审批通过日期`) >= ifnull(l.`订单失销日期`,'1970-01-01') AND l.`订金类型`='意向金' and l.订单状态 <> '退订C' and l.订单状态 is not null  THEN '12'\n " +
                "      ELSE '10'\n" +
                "      END AS 新车销售状态,   \n" +
                "    l.`销售类型` `销售类型`,     \n" +
                "    e.`实付金额` `实际到账总额`,     \n" +
                "    l.`订单审批通过日期` 排序时间 ,    \n" +
                "    d.`报价编号` 报价编号,    \n" +
                "    d.初始报价单号    \n" +
                "  from 报价单表 AS d     \n" +
                "  INNER JOIN `报价付款详细` AS e ON d.`报价编号` = e.`报价编号`     \n" +
                "  LEFT JOIN `LSH库存表` AS a ON a.`库存编号` = d.`库存编号`     \n" +
                "  INNER JOIN `LSH车型` AS b ON b.`车型编号` = d.`车型编号`     \n" +
                "  INNER JOIN `用户` AS k ON d.`销售顾问` = k.`用户名`     \n" +
                "  INNER JOIN `报价订单表` AS l ON d.`订单编号` = l.`订单编号`      \n" +
                "  INNER JOIN `顾客记录汇总` AS n ON d.`顾客编号` = n.`顾客编号`     \n" +
                "  INNER JOIN `finance_deposit_check` AS fd ON d.`报价编号` = fd.`quota_id`     \n" +
                "  left join  报价交车表 bjjc on bjjc.编号=d.交车  "+
                "  where d.许可=:permit    \n");

        if(!Strings.isNullOrEmpty(re.IntentionalVehicle)){
            // sql.append(" and a.意向车型 like '%"+re.IntentionalVehicle+"%' ");
            sql.append( " and  (  b.`品牌` LIKE  '%"+re.IntentionalVehicle+"%' " +
                    "OR b.`级别` LIKE  '%"+re.IntentionalVehicle+"%' " +
                    "OR b.`车型` LIKE  '%"+re.IntentionalVehicle+"%' " +
                    "OR b.`车型详细` LIKE  '%"+re.IntentionalVehicle+"%'  )");
        }
        sql.append("    ORDER  by  d.`报价编号` desc  ) a   where 1=1  ");
        if(!Strings.isNullOrEmpty(re.paymentPurpose)){
            sql.append(" and a.付款目的='"+re.paymentPurpose+"' ");
        }
        if(!Strings.isNullOrEmpty(re.saleType)){
            sql.append(" and  a.销售类型 ='"+re.saleType+"' ");
        }
        if(!Strings.isNullOrEmpty(re.newCarSaleStatus)){

            sql.append(" and  a.新车销售状态 ='"+re.newCarSaleStatus+"' ");
        }

        if(!Strings.isNullOrEmpty(re.vin)){

            sql.append(" and  a.`车架号(VIN)` like :vin ");
            params.put("vin","%"+re.vin+"%");
        }
        if(!Strings.isNullOrEmpty(re.cusName)){
            sql.append(" and  a.客户名称 like '%"+re.cusName+"%' ");
        }
        pg=this.getPage(sql.append("  GROUP BY  a.初始报价单号   order by 状态 desc, 排序时间 desc  ").toString(),params,re.getPageSize(),re.getPageIndex());
        return pg;
    }

    // 根据初始报价单号查找退票审批记录
    @Override
    public List<PushApprovalWorkFlowModel> getRefundRecordByInitQuotationNum(String initQuotationNum) {
        String sql = "select * from pushapprovalworkflow where type = " + ApprovalWorkFlowEnum.TICKET_RETREAT+ " and approvalStatus in('1','3') and quotedPriceCodeOrVIN in " +
                "    (   select 报价编号 from 报价单表 where 初始报价单号 = :initQuotationNum " +
                "     ) order by id desc";
        Map params = new HashMap();
        params.put("initQuotationNum",initQuotationNum);
        return this.getList(sql,params,PushApprovalWorkFlowModel.class);
    }

    // 根据指定的报价单，获得所有的车型
    @Override
    public List<Lsh车型Model> getCarTypeByQuotationNums(Integer startQuotationNum, Integer endQuotationNum) {
        String sql = "select * from lsh车型 where 车型编号 in (select 车型编号 from 报价单表 where 报价编号 between :startQuotationNum and :endQuotationNum and 是否还原点= '是' ) ";
        Map params = new HashMap();
        params.put("startQuotationNum",startQuotationNum);
        params.put("endQuotationNum",endQuotationNum);
        return this.getList(sql,params,Lsh车型Model.class);
    }

    @Override
    public List<PushApprovalWorkFlowModel> getApprovalRecordByCondition(String initQuotationNum, int type, Integer startQuotationNum, Integer endQuotationNum){
        String sql = "select * from pushapprovalworkflow where type = " + type + " and approvalStatus in('1','3') and quotedPriceCodeOrVIN in " +
                "    (   select 报价编号 from 报价单表 where (报价编号 between :startQuotationNum and :endQuotationNum ) and 初始报价单号 = :initQuotationNum " +
                "     ) order by id desc";
        Map params = new HashMap();
        params.put("initQuotationNum",initQuotationNum);
        params.put("startQuotationNum",startQuotationNum);
        params.put("endQuotationNum",endQuotationNum);
        return this.getList(sql,params,PushApprovalWorkFlowModel.class);
    }

    @Override
    public List<PushApprovalWorkFlowModel> getBeforeModifyOrderApprovalRecord(Integer quotationNum, String initQuotationNum) {
        String sql = "select * from pushapprovalworkflow where type in(" + ApprovalWorkFlowEnum.ORDER_UPDATE + "," + ApprovalWorkFlowEnum.ORDER_INTENTION_UPDATE + ") and quotedPriceCodeOrVIN in ( " +
                     "  select 报价编号 from 报价单表 where  报价编号 < :quotationNum and 初始报价单号 = :initQuotationNum  " +
                     ")";
        Map params = new HashMap();
        params.put("quotationNum",quotationNum);
        params.put("initQuotationNum",initQuotationNum);
        return this.getList(sql,params,PushApprovalWorkFlowModel.class);
    }

    @Override
    public List<PushApprovalWorkFlowModel> getApprovalRecordByUserNameAndStatus(String userName, String status) {
        String sql = "select * from pushapprovalworkflow  " +
                "where approvalStatus = :status  " +
                "and username in( " +
                "   select a.用户名 from 用户 a,t_account_info b where a.account_id = b.account_id and b.account_name = :userName " +
                ") ";
        Map params = new HashMap();
        params.put("userName",userName);
        params.put("status",status);
        return this.getList(sql,params,PushApprovalWorkFlowModel.class);
    }
}